Split multiple strings in excel vba

Question

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

you code is working for me as expected. What is wrong in your opinion? — Kazimierz Jawor
– Kazimierz Jawor, Commented Nov 17, 2013 at 20:33

Siddharth Rout · Accepted Answer · 2013-11-17 21:15:06Z

1

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)

edited Nov 17, 2013 at 21:15

answered Nov 17, 2013 at 20:56

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Split multiple strings in excel vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related