Splitting a range with several cells

[consth21]

I was wondering if anyone could help me with this code. I am trying to take a couple of cells (A1:A3) and put them into different cells, 1 right after another. When I try this with the code below, it only does the range of A1.

Sub wordbrk()
Dim x, c As Range, y As Integer
x = Split(Range("A1:A3"), " ")
For Each c In Range("H1:L100")
If y > UBound(x) Then Exit For
c = Trim(x)
y = y + 1
Next
End Sub

Thank you!!

 

[Juan Pablo González]

I get an error in the Split() line, because

Range("A1:A3")

returns a Variant() array, not a string, as Split() expects... try using something like

x = Split(Range("A1").Value & " " & Range("A2").Value & " " & Range("A3").Value, " ")

 

[consth21]

Great that worked!! There is one problem though. When I did it, it stuck the print outs together and only seperated them by one cell instead of making like A2 go to the next row. Can I change this?

 

[Scott Huish]

See original post here:
http://www.mrexcel.com/board2/viewtopic.php?p=624705