Text to Rows
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(WorksheetFu nction.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.
--
Rick (MVP - Excel)
"Vinay" wrote in message
...
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
|