For Eachcell in Range tranpose comma seperated values to a list in D

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

This works great for cell A1 to rows in D.
How do I modify the code to do A1:Ax into column D down as far as needed?

My "Error 400" code attempt at it follows this working code.

Thanks.
Howard

Option Explicit

Sub SuperSplit()
Dim vArray As Variant
Dim x As Long
vArray = Split(Application.Transpose(Range("A1")), ", ") '" / ")
For x = 0 To UBound(vArray)
Range("D1").Offset(x, 0).Value = vArray(x)
Next
End Sub


Not working:

Sub SuperSplitX()

Dim vArray As Variant
Dim x As Long
Dim c As Range
Dim y As Long
y = 1
For Each c In Range("A1:A3")
vArray = Split(Application.Transpose(Range("A:" & y)), ", ") '" / ")
y = y + 1
For x = 0 To UBound(vArray)
Range("D1").End(xlUp).Offset(x, 0).Value = vArray(x)
Next
Next

End Sub
 
Hi Howard,

Am Sun, 13 Oct 2013 14:40:03 -0700 (PDT) schrieb Howard:
This works great for cell A1 to rows in D.
How do I modify the code to do A1:Ax into column D down as far as needed?
Click to expand...

try:
Sub Test()
Dim varOut As Variant
Dim rngC As Range
Dim FECell As Range
Dim LRow As Long

LRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A1:A" & LRow)
varOut = Split(rngC, ", ")
Set FECell = Cells(Rows.Count, 4).End(xlUp).Offset(1, 0)
FECell.Resize(rowsize:=(UBound(varOut) + 1)) = _
Application.Transpose(varOut)
Next
End Sub


Regards
Claus B.
 
Hi again,

Am Mon, 14 Oct 2013 00:06:32 +0200 schrieb Claus Busch:

or write all cells in one string and then transpose:
Sub Test2()
Dim varOut As Variant
Dim rngC As Range
Dim FECell As Range
Dim LRow As Long
Dim myString As String

LRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A1:A" & LRow)
myString = myString & rngC & ", "
Next
myString = Left(myString, Len(myString) - 2)
varOut = Split(myString, ", ")
Set FECell = Cells(Rows.Count, 4).End(xlUp).Offset(1, 0)
FECell.Resize(rowsize:=(UBound(varOut) + 1)) = _
Application.Transpose(varOut)
End Sub


Regards
Claus B.
 
Hi Howard,

Am Mon, 14 Oct 2013 00:11:32 +0200 schrieb Claus Busch:
or write all cells in one string and then transpose:
Click to expand...

the second suggestions is faster.


Regards
Claus B.
 
Hi Howard,



Am Mon, 14 Oct 2013 00:11:32 +0200 schrieb Claus Busch:






the second suggestions is faster.





Regards

Claus B.
Click to expand...

Both work well!

I will use your two and the working one I submitted for some good study material for me.

Thanks, Claus.
Regards,
Howard
 
Back
Top