Data into multiple cells and rows

[consth21]

I was wondering how I would go about putting a cell's output into multiple cells and rows. For example, I would like to do a repeat function in a cell, depending on numbers in a previous sheet.
=REPT("thickness, ",A1-1)&TEXT("thickness",1)
A1 ranges from 5-56. In all cases, the output of this cell is greater than 5 columns wide. I can only have an output that is 5 columns and the remaining needs to go into rows. How would I be able to go about doing this? I would like output to be something like this....
thickness, thickness, thickness, thickness, thickness,
thickness, thickness

Thank you in advance. I just found out about this website today and there is some great information on it. It's too bad that I didn't find out about earlier.

Heidi

 

[consth21]

Just wondering if someone might be able to help me.....PLEASE!!!???!!!

Any help would be greatly appreciated.
Have a great day/night and thank you in advance!

 

[Ron Morris]

Hi,

Porbably not quite what you want to hear, but couldn't you just merge the 5 columns with say 5 rows and set the wrap text checkbox to true and align to top left?

Ron

 

[erik.van.geit]

Heidi,

Could this give you a start?

Considering that the text to distribute is in cell A1, that we're using 5 columns and that the textseparator is the space.
You change all those items within the macro.

kind regards,
Erik

Sub distribute_words_to_cells()
'Erik Van Geit
'050202

Dim text As String
Dim words(99) As Variant
Dim i As Integer
Dim sp As Integer 'space or other characters
Dim nrcol As Integer

text = Range("A1") & " "
i = 0
nrcol = 5 'to how many columns will the data be ditributed

Do
i = i + 1
sp = InStr(text, " ")
words(i) = Left(text, sp - 1)
text = Right(text, Len(text) - sp)
Loop While text <> ""

For i = 1 To i
Cells(Int((i - 1) / nrcol) + 1, (i - 1) Mod nrcol + 1) = words(i)
Next i

End Sub

 

[consth21]

Well, this sounds exactly like what I need, however now I can't even get the macro to work. Because I have never done this, maybe I am doing it incorrectly. I pasted what you wrote into visual basic and then went back to my cell where the "long" output was. I ran the macro, however nothing happened. Could you help me with this? Also, is there any way that I can add another character and some spaces to the beginning of each row, say a "& (and then the text)?
Thank you so much for your help.
Heiid

 

[erik.van.geit]

Heidi,

Let's first get it to work and then resolve the other questions (remind me when I would forget)

Nothing happened?
What was in your cell A1? Are there single spaces between the words?
Step through the macro with F8, go with your mouse on the different variables (i - sp - words(i) - text) and check the values...

Put in your cell A1 "a b c d e f": it should end up with
A1 : a, B1 : b, ...

can you manage something yet?

kind regards,
Erik

 

[consth21]

Actually that doens't work either. I am putting this ="a b c d e f" into cell A1. I am then going to tools/macros/run macros and nothing happens. I have tried this both in a different cell and the same cell, but no luck. Any thoughts?
Thank you!

 

[erik.van.geit]

I'm quite sure you're not running the macro

Put this at the beginning of your macro
msgbox "distribute_words_to_cells"
when you run the macro a message should popup

or (and) this
Range("A1") = "a b c d e f g h"
when you run the macro at least cell A1 must be filled with the data

Did you step through your macro with F8 ?

When you goto menu Tools / Macro / Security
what do you see then (what option is checked?)

which version of Excel are you running?

kind regards,
Erik

 

[consth21]

Hello Erik,
So, I tried adding the message box and that worked, I got one. However when I put the range information in, nothing different happened. Right now, on a seperate worksheet, I have ="a b c d e f g h" in A1. I did step through the macro with F8, however I wasn't quite sure what I was looking at. I am new to programming and wasn't sure what it all meant. I told you I didn't know anything Anyway, I am using office xp. When I checked the security, it was set to medium, which seems like the problem doesn't arise there. Any other input for the beginner?

Thank you again,
Worse case, if it takes me forever to learn this, atleast I'm learning several other things in the process.
Heidi

 

[erik.van.geit]

Heidi,

OK, let's start again.
Office XP no problem of course, security settings OK.

please open a new workbook
paste this code and run it

Sub distribute_words_to_cells()
'Erik Van Geit
'050202

Dim text As String
Dim words(99) As Variant
Dim i As Integer
Dim sp As Integer 'space or other characters
Dim nrcol As Integer
Range("A1") = "a b c d e f g h" '''''added line for testing

text = Range("A1") & " "
i = 0
nrcol = 5 'to how many columns will the data be ditributed

Do
i = i + 1
sp = InStr(text, " ")
words(i) = Left(text, sp - 1)
text = Right(text, Len(text) - sp)
Loop While text<> ""

For i = 1 To i
Cells(Int((i - 1) / nrcol) + 1, (i - 1) Mod nrcol + 1) = words(i)
Next i

End Sub

what do you get ?
I get this

Map1
	A	B	C	D	E
1	a	b	c	d	e
2	f	g	h
Blad1

"stepping through the code with F8": I should have mentioned too to put aside the VBE-Window so you can see what happens on your sheet

first you'll see cell A1 get "a b c d e f g"
then you'll see nothing for a while since the code is calculating the "words"
(Do .... Loop While text<> "")
starting from For i = 1 To i
(here i has already a value 8, it will start again on 1 going to 8)
your code will loop for all words and put them in the cells
isn't it?

kind regards,
Erik