Skip to main content
Support is Online
We're back! We are here to assist you. Please be patient, we will respond to your tickets shortly.
Official support hours
Monday To Friday
From 09:00 To 17:30
  Tuesday, 24 December 2019
  1 Replies
  11.8K Visits
0
Votes
Undo
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
3 years ago
·
#2048
0
Votes
Undo
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.
  • Page :
  • 1
There are no replies made for this post yet.