Sub SplitText()
Dim r, rows, i, j, s, Data
Data = Range("Sheet1!A2:I6") 'change this to point to your data
ReDim Result(1 To UBound(Data) * 1.5, 1 To 9) 'allows for 50% extra rows, increase if necessary
For r = LBound(Data) To UBound(Data)
s = Split(Data(r, 9), ",") 'split column I by commas
For i = 0 To UBound(s)
rows = rows + 1
For j = 1 To 8
Result(rows, j) = Data(r, j)
Next j
Result(rows, 9) = s(i)
Next i
Next r
'change the range address below to point to the first cell you want filled with results
Range("Sheet2!A1").Resize(rows, 9) = Result
End Sub
I still haven't figured this out.I updated per your notes and receive a runtime error.
What exactly was the error message that you received?
Did it give you the option to "Debug?
If so, what line of code does it highlight if you click the "Debug" button?
I'm getting the same error.
ReDim Result(1 To UBound(Data) * 1.5, 1 To 9) 'allows for 50% extra rows, increase if necessaryCan you compile the code?
If it compiles all right, tried stepping in to the code and going through the code line-by-line, pressing the F8 key, and see which line of code it errors out on.
That shows us what we need to focus on.