VBA – Need to parse a 2nd column and tack to the bottom, but stuck

You can do it like this:

Sub Tester()

    Dim ws As Worksheet, rw As Long, rwOut As Long, arr, col As Long, v, e
    
    Set ws = ActiveSheet 'or whatever
    
    ws.Cells(1, 8).Resize(1, 5).Value = _
          Array("Category", "SubCat", "Notes", "Label", "Rank")
    
    rwOut = 2
    For col = 4 To 6
        For rw = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
            v = ws.Cells(rw, col).Value
            If Len(v) > 0 Then
                arr = Split(v, ",")
                For Each e In arr
                    'Cat, subcat & notes
                    ws.Cells(rwOut, 8).Resize(1, 3).Value = _
                                     ws.Cells(rw, 1).Resize(1, 3).Value
                    
                    ws.Cells(rwOut, 11).Value = Trim(e)

                    ws.Cells(rwOut, 12).Value = _
                                     Array("Low", "Med", "High")(col - 4)

                    rwOut = rwOut + 1
                Next e
            End If
        Next rw
    Next col

End Sub

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top