this one is better explained through pictures:
I want to get from this:
and have the code make this: (where it is copying the entire row if it finds a cell in that row that contains the string ", " and then inserts 2 new copied rows beneath the target row...)
the code I am playing around with looks to be on the right path, but I cant get it to find the right character string (", " comma followed by a space) in the right column (column C)...
thats my first hurdle... once I get that right, then I need to figure out how to make the correct number of copies of the target row and insert the copied row beneath it.
Is this even possible or am I asking too much of VBA to accomplish? Thanks for any help.
current code:
I want to get from this:
and have the code make this: (where it is copying the entire row if it finds a cell in that row that contains the string ", " and then inserts 2 new copied rows beneath the target row...)
the code I am playing around with looks to be on the right path, but I cant get it to find the right character string (", " comma followed by a space) in the right column (column C)...
thats my first hurdle... once I get that right, then I need to figure out how to make the correct number of copies of the target row and insert the copied row beneath it.
Is this even possible or am I asking too much of VBA to accomplish? Thanks for any help.
current code:
VBA Code:
Private Sub CommandButton1_Click()
Dim rng As Range, Lstrw As Long, RecordRow As Long, c As Range
Dim SpltRng As Range
Dim i As Integer
Dim Orig As Variant
Dim txt As String
Lstrw = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("C3:C" & Lstrw)
'
For Each c In rng.Cells
Set SpltRng = c.Offset(, 3)
txt = SpltRng.Value
Orig = Split(txt, ",")
For i = 0 To UBound(Orig)
Cells(Rows.Count, "B").End(xlUp).Offset(1) = c
Cells(Rows.Count, "B").End(xlUp).Offset(, 1) = Orig(i)
Next i
Next c
End Sub
