CODEWITHSUNDEEP
vbaexcel
Terataz
Terataz

Reputation: 185

Split multiple strings in excel vba

I have an excel with some data separated by ";" in a cell. I need to split that cell, and some subsequent one, so that splitted content will go each to a new line each. Example:

Column f    Column j     Column k
a;b;c       d;e;f        g;h;i

Should become

a         d       g
b         e       h
c         f       i

This is what i have, but not working:

Sub tgr()

Dim rindex As Long
Dim saItem() As String
Dim sbItem() As String
Dim scItem() As String

For rindex = Cells(Rows.Count, "F").End(xlUp).Row To 1 Step -1
    If InStr(Cells(rindex, "F").Value, ";") > 0 Then
        saItem = Split(Cells(rindex, "F").Value, ";")
        sbItem = Split(Cells(rindex, "J").Value, ";")
        scItem = Split(Cells(rindex, "K").Value, ";")
        Rows(rindex + 1 & ":" & rindex + UBound(saItem)).Insert
        Cells(rindex, "F").Resize(UBound(saItem) + 1).Value = WorksheetFunction.Transpose(saItem)
        Cells(rindex, "J").Resize(UBound(sbItem) + 1).Value = WorksheetFunction.Transpose(sbItem)
        Cells(rindex, "K").Resize(UBound(scItem) + 1).Value = WorksheetFunction.Transpose(scItem)
    End If
Next rindex

End Sub

Thanks for your help

Upvotes: 0

Views: 279

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149325

You are using the wrong array for Col K. I guess you copy pasted and forgot to change it? :)

Replace the line

Cells(rindex, "K").Resize(UBound(sbItem) + 1).Value = WorksheetFunction.Transpose(sbItem)

with

Cells(rindex, "K").Resize(UBound(scItem) + 1).Value = WorksheetFunction.Transpose(scItem)

Upvotes: 1

Related Questions