Using the Convert comma separate values VBA
By Guest on Tuesday, 24 December 2019
Posted in Excel
Replies 1
Likes 0
Views 12.3K
Votes 0
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:
[font=Calibri]Current[/font]
[font=Calibri]Trees[/font][font=Calibri]Dogwood,Ash,Maple,Elm,Apple[/font]
[font=Calibri]Need[/font]
[font=Calibri]Trees[/font][font=Calibri]Dogwood[/font]
[font=Calibri]Trees[/font][font=Calibri]Ash[/font]
[font=Calibri]Trees[/font][font=Calibri]Maple[/font]
[font=Calibri]Trees[/font][font=Calibri]Elm[/font]
[font=Calibri]Trees[/font][font=Calibri]Apple [/font]
Thank you
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.
Lewis-H
·
3 years ago
·
0 Likes
·
0 Votes
·
0 Comments
·
View Full Post