Text to Rows

[Vinay]

Hi, I am newbie in excel Macros. I have a macro that works well if used on a
cell. BUT if I want to run it on every cell within a Range, I get an error -
"Invalid, Next control Variable reference". Please help. see below

Sub NewOne()
Dim x As Integer
Dim y As Integer

' Hardcoded range A1 to C2
NumRows = Range("A1", "A2").Rows.Count
NumCols = Range("A1", "C1").Cols.Count

Range("A1").Select

For x = 1 To NumRows Step 1
For y = 1 To NumCols Step 1
'Text to multiple rows
'-------------
SrcData = Cells(x, y).Value
OutPutData = Split(SrcData, ";")
'OutPutData = Split(SrcData, Chr(10))

For SplitData = 0 To UBound(OutPutData)
Range("J" & SplitData + 1) = OutPutData(SplitData)
'Cells(SplitData + 1, "a") = OutPutData(SplitData)
'-------------
Next y
Next x

End Sub

 

[Ryan H]

First, you have a lot of variables that are undeclared. I would highly
recommend you declare your variables. Second, I would highly recommend you
put Option Explicit at the top of you modules which will catch a lot of
errors in your code. I cleaned up your code a bit. Try this. Hope this
helps! If so, let me know, click "YES" below.

Option Explicit

Sub NewOne()

Dim x As Integer
Dim y As Integer
Dim NumRows As Long
Dim NumCols As Long
Dim SrcData As String
Dim OutPutData As Variant
Dim SplitData As Long

' Hardcoded range A1 to C2
NumRows = Range("A1", "A2").Rows.Count
NumCols = Range("A1", "C1").Columns.Count

Range("A1").Select

For x = 1 To NumRows Step 1
For y = 1 To NumCols Step 1
'Text to multiple rows
'-------------

SrcData = Cells(x, y).Value
OutPutData = Split(SrcData, ";")
'OutPutData = Split(SrcData, Chr(10))

For SplitData = 0 To UBound(OutPutData)
Range("J" & SplitData + 1) = OutPutData(SplitData)
'Cells(SplitData + 1, "a") = OutPutData(SplitData)
'-------------
Next SplitData
Next y
Next x

End Sub

 

[Rick Rothstein]

Besides the excellent advice Ryan gave you, you should also describe what
you have and what you are trying to do with it (perhaps giving examples of
before and after data) so we don't have to guess from your non-working code
what you might be trying to accomplish. I'm guessing that each cell in A1:C2
contains one or more text items delimited by semi-colons and that you want
each individual item listed in Column J, one item per cell. If that is
correct, you can do this without any looping whatsoever like this...

Sub ListA1toC2OutIntoColumnJ()
Dim C As Variant
C = Split(Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose( _
Range("A1:C1"))), ";") & ";" & Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Range("A2:C2"))), ";"), ";")
Range("J1:J" & UBound(C) + 1).Value = WorksheetFunction.Transpose(C)
End Sub

If you describe in detail what you are actually trying to do, I'm sure I can
generalize this code for you so that it is not trapped to A1:C2, but I don't
want to waste my time doing that if my guess is not correct or if the range
is not a contiguous one.