VBA assistance to separate out strings between colons in cells and copy each individu

[tyantorno]

Hello,

I am a novice to intermediate VBA guy. I have code to split semi colon rows but it seems to affect the entire row of data rather than just inserting down the cell only.

row 1 a 1 x
row 2 b 2,3 y
row 3 c 4 z

is how the the data looks and I would like it to

row 1 a 1 x
row 2 b 2 y
row 3 b 3 y
row 4 c 4 z

Any assistance would be greatly appreciated. Thank you.

[Ken Puls]

Since I'm not seeing any semi colons in the above, I'm having a real hard time figuring out where to start here.

Can you post a sample of what some raw data might look like, what the output should look like, and the code you're currently using? You can attach a workbook to this post by going to the "Go Advanced" post reply button.

[tyantorno]

Hello Ken,

I am terribly sorry, I meant to put in semi colons in message. I have uploaded the raw data sample and the excel spread sheet I am using. I think it is password protected, ty is the password, not a real strong one lol.
Anyway the data I have is tricky. The first column in comp code so it does not matter much. The second column is unique identifiers for column f and must coincide. Column C is dollar amounts which must also coincide, however, that data will be edited only for the copied cells, or cells with semi colons split out. Column D also must coincide with the return because the state whether a check is created or electronic filing. Column E also needs to coincide because that is the clients account number, and there can but duplicate numbers in the column even for different states.

Finally column F is the actual return name which needs to coincide as well. The code I have provided goes through many steps. I have tried to leave good comments as well. I have been able to get column a/b/e/f to all coincide but because of the splitting out column c and d will be off the same number of copied cells so I have to go in and delete cells manually to have it line up. I am 90 percent done I just cant seem to figure out how to get c and d to work. Thanks!

[Ken Puls]

It's Step1_7 that's the issue, correct?

Try this:

Sub Step1_7()
    Dim ary() As String
    Dim cnt As Long
    Dim LastRow As Long
    Dim i As Long
    
    With Worksheets("Sheet2")
        LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
            For i = LastRow To 1 Step -1
                ary() = Split(.Cells(i, "E"), "; ")                         ' Added space so can copy AJ-JEFF to all rows
                                                               ' ary = Split(.Cells(i, "F"), ";") '' If use ";" then only copies first row
        cnt = UBound(ary()) - LBound(ary()) + 1
              If cnt > 1 Then
                 .Rows(i).Copy                                 ' This was added to copy row
             .Rows(i).Font.Color = vbGreen
          .Rows(i + 1).Resize(cnt - 1).Insert
        .Cells(i, "E").Resize(cnt) = Application.Transpose(ary())
            End If
        Next i
    End With
    
    Application.CutCopyMode = False                             ' This was added when copy and paste may always need this
Call Step1_8
End Sub