normalUsing the Convert comma separate values VBA

More
1 month 6 days ago #2519 by Lewis-H
Replied by Lewis-H on topic Using the Convert comma separate values VBA
Public Sub textToColumns()
Set ARange = Range("A:A")
Set BRange = Range("B:B")
Set CRange = Range("C:C")
Set DRange = Range("D:D")
Dim arr() As String
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set out = Worksheets.Add
out.Name = "out"
outRow = 2
For i = 2 To lr
    arr = Split(ARange(i), ",")
    For j = 0 To UBound(arr)
        out.Cells(outRow, 1) = Trim(arr(j))
        out.Cells(outRow, 2) = BRange(i)
        out.Cells(outRow, 3) = CRange(i)
        out.Cells(outRow, 4) = DRange(i)
        outRow = outRow + 1
    Next j
Next i
End Sub
I didn't do the headers or deal properly with the output sheet but you can see basically what's going on.

Please Log in or Create an account to join the conversation.

More
8 months 3 weeks ago #1981 by Boater
Using the VBA to convert my comma string into rows works great- but now I need to match the break out with the value in column A. Example
Colum A has "Trees: Column B was the comma string: Dogwood,Ash,Maple,Elm,Apple.
How do I display as:
Current
Trees Dogwood,Ash,Maple,Elm,Apple
Need
Trees Dogwood
Trees Ash
Trees Maple
Trees Elm
Trees Apple 
Thank you

Please Log in or Create an account to join the conversation.

Moderators: jaychivoExcelfansqiuhongkun