splitting text on multiple delimiter

[TazRMan]

Hi. I need someone to check my code. I am having trouble.

I have the following:
1a,1b,1c,1d 2a,2b,2c,2d 3a,3b,3c,3d 4a,4b,4c,4d 5a,5b,5c,5d
6a,6b,6c,6d 7a,7b,7c,7d 8a,8b,8c,8d 9a,9b,9c,9d 10a,10b,10c,10d
11a,11b,11c,11d 12a,12b,10c,12d 13a,13b,13c,13d 14a,14b,10c,14d 15a,15b,15c,15d

needs to be split into another worksheet as
1a 1b 1c 1d
2a 2b 2c 2d

etc...

here is the code

Sub Test()
Dim wb As Workbook
Dim wf As Worksheet
Dim wt As Worksheet
Dim i As Long
Dim t As Long
Dim x As Long
Dim y As Long
Dim z As Long
Dim txt As String
Dim Splt_text As Variant


Set wb = ActiveWorkbook
Set wf = wb.Sheets("From_w")
Set wt = wb.Sheets("To_w")
z = 2
y = wf.Cells(wf.Rows.Count, "A").End(xlUp).Row

wt.Rows("2:65536").Clear

For i = 2 To y
For x = 1 To 5
txt = wf.Cells(i, x).Value
Splt_text = Split(txt, ",")
For t = 0 To UBound(Splt_text)
wt.Cells(z, t + 1).Value = Splt_text(i)
Next t
z = z + 1

Next x


Next i

End Sub

 

[TazRMan]

I found my error in line "wt.Cells(z, t + 1).Value = Splt_text(i)"

Should read as follows :

wt.Cells(z, t + 1).Value = Splt_text(t)

 

[MickG]

Try this for results on sheet 2.

[COLOR="Navy"]Sub[/COLOR] MG06Mar21
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Sp [COLOR="Navy"]As[/COLOR] Variant, nSp [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Sp = Split(Dn.Value, " ")
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
        nSp = Split(Sp(n), ",")
        c = c + 1
        Sheets("Sheet2").Cells(c, "A").Resize(, UBound(nSp) + 1).Value = nSp
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick