OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Making Macros

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
JudeCW
Newbie
Newbie


Joined: 19 Apr 2009
Posts: 2

PostPosted: Sun Apr 19, 2009 2:45 pm    Post subject: Making Macros Reply with quote

Hi, just downloaded OO and am currently having a little problem.

I was just wondering if anyone knew how to create a macro that takes each letter from a string in one cell, and puts each letter into its own cell. For example, if I pasted this paragraph into cell A1 then click my macro, then I appears in A3, W appears in B3, A appears in C3.. up to N in N3, at which point it starts a new line and D appears in A4, etc...

Also, I've been doing the reverse with =concatenate(A1;B1;C1) etc (p.s. is there a faster way to get it to do a massive amount of cells, as =concatenate(A1:Z1) doesn't seem to work) but it's not picking up spaces, any ideas?

Thanks, Jude


Last edited by JudeCW on Mon Apr 20, 2009 9:20 am; edited 1 time in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Apr 20, 2009 12:33 am    Post subject: Reply with quote

=MID($A$1;COLUMN(A1);1)
Copy to the right
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
JudeCW
Newbie
Newbie


Joined: 19 Apr 2009
Posts: 2

PostPosted: Mon Apr 20, 2009 9:22 am    Post subject: Reply with quote

That's perfect, thank you. There's still the problem of doing the reverse though, apparantly there is a function called MCONCAT for excel which is a mass concatenate, anyone know if there is an equivalent for Open Office? Also, could anyone convert my macro made in visual basic so that it works in Open Office Basic? Here it is.

Function Kasiski(texty as String, ciphertext As String, shift As Integer) As Integer
Dim texty As String
Dim count As Integer
Dim length As Integer
length = Len(ciphertext)
texty = ciphertext
count = 0
For i = 1 To length - shift
If Mid(ciphertext, i, 1) = Mid(texty, i + shift, 1) Then count = count + 1
Next i
Kasiski = count
End Function

It just says "Basic runtime error. Argument is not optional" and highlights the line "for i = 1...."
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Apr 20, 2009 10:05 am    Post subject: Reply with quote

Quote:
Also, could anyone convert my macro made in visual basic so that it works in Open Office Basic? Here it is.

If that were your macro you would know how to call it.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Apr 20, 2009 10:51 am    Post subject: Reply with quote

Try my version instead.
Install: Alt+F11, My Macros>Standard>AnyModule,[Edit...], paste.
Instead of "My Macros" you may use the respective document's container. In any case you've got to use library "Standard" for userdefined cell functions.
Code:
Function BAS_JOIN(a(), optional s$)
REM Requires a *horizontal* vector. Default separator is space.
REM For vertical vectors call BAS_JOIN(TRANSPOSE(A1:A5))
if isMissing(s) then s = " "
iLB = lBound(a(),2) : iUB = uBound(a(),2)
dim x(iLB to iUB)
for i = iLB to iUB
   x(i) = a(1, i)
next
BAS_JOIN = join(x, s)
End Function

Horizontal vector concatenated with semicolon:
=BAS_JOIN(A1:J1;";")
Vertical vector concatenated with default space:
=BAS_JOIN(TRANSPOSE(A1:A9))
The simple function expects a horizontal vector. Transpose turns a vertical vector into a horizontal one.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group