Results 1 to 4 of 4

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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!
    Attached Files Attached Files

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    It's Step1_7 that's the issue, correct?

    Try this:
    Code:
    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
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •