0

I have a string of text that I want to split up. But at the same time when the string is split, I need it to pull the following data that is associated with it. I have tried transposing, and I have tried the split function. But it does not do what I need it to do. Any ideas or suggestions that I can try. Here is an example of what I am trying to accomplish:

enter image description here

This is what I currently have and changed and tried to modify from the first piece of code. Still cant figure it out: 

Sub Test()
Dim rng As Range, Lstrw As Long, c As Range, d As Range
Dim SpltRng As Range
Dim i As Integer
Dim j As Integer
Dim Orig As Variant
Dim txt As String

Lstrw = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A2:A" & Lstrw)

For Each c In rng.Cells
    Set SpltRng = c.Offset(, 1)
    txt = SpltRng.Value
    Orig = Split(txt, ",")

Lstrw = Cells(Rows.Count, "B").End(xlUp).Row
Set rng = Range("B2:B" & Lstrw)

For Each d In rng.Cells
Set SpltRng = d.Offset(, 1) + 1


    For i = 0 To LBound(Orig)
        Cells(Rows.Count, "L").End(xlUp).Offset(1) = c
        Cells(Rows.Count, "L").End(xlUp).Offset(, 1) = Orig(i)

      For j = 0 To LBound(Orig)
        Cells(Rows.Count, "L").End(xlUp).Offset(1) = d
        Cells(Rows.Count, "L").End(xlUp).Offset(, 1) = Orig(j)
            Next j

         Next i

       Next d
   Next c

 End Sub
Community
  • 1
  • 1
Sam
  • 75
  • 1
  • 1
  • 8
  • 3
    Did you try to modify the code given on your [LAST](http://stackoverflow.com/questions/35439099/how-to-split-a-single-cell-into-multiple-rows-and-add-another-row) question? If so please show what you tried to change. Edit your original post to show you at least attempted it. – Scott Craner Feb 18 '16 at 22:03
  • 1
    The string split and join functions are the best solution for this kind of problem. Please post what you have tried, the result, and point out the problem areas if not obvious. Thanks. – Yogi Feb 18 '16 at 22:17
  • Why does splitting the string "test1 test2 test3" cause c7, c8 & c9 to be History (original value of row 2 where original string was), but the string "Tim Robert" causes c10 to be Tim and c11 to be Robert and not "People", which was the original data from row 3? Please explain your algorithm for determining the new value for column C. – Scott Marcus Feb 18 '16 at 22:28

1 Answers1

3

You are way over thinking it, you only need to add one line to the code rovided by @Davesexcel:

Sub ChickatAH()
Dim rng As Range, Lstrw 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("A2:A" & Lstrw)

For Each c In rng.Cells
    Set SpltRng = c.Offset(, 1)
    txt = SpltRng.Value
    Orig = Split(txt, " ")

    For i = 0 To UBound(Orig)
        Cells(Rows.Count, "L").End(xlUp).Offset(1) = c
        Cells(Rows.Count, "L").End(xlUp).Offset(, 1) = Orig(i)
        'New Line
        Cells(Rows.Count, "L").End(xlUp).Offset(, 2).Resize(, 3).Value = c.Offset(, 2).Resize(, 3).Value
    Next i

Next c

End Sub
Scott Craner
  • 137,650
  • 9
  • 45
  • 77
  • Thanks for your help @ScottCraner. You have any ideas or suggestions where I can learn more VBA on my own? – Sam Feb 19 '16 at 21:22
  • 1
    @Sam Did it work? If so please mark as correct. As to where to learn, I did most of mine on line. It is a matter of learning the correct question to ask in google. Instead of trying to get the whole picture, break it down into parts. For example your first question, how to loop in vba? Next how to split text string vba? When you want to do something think about the steps then google the steps. – Scott Craner Feb 19 '16 at 21:25