Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text to Rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text to Rows
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 -- Cheers, Ryan "Vinay" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find text and copy selected rows from text and loop | Excel Programming | |||
Text to Rows and then Insert Blank Rows | Excel Discussion (Misc queries) | |||
Delete rows with numeric values, leave rows with text | Excel Programming | |||
delete empty rows between rows with text | Excel Discussion (Misc queries) | |||
extracting text from within a cell - 'text to rows@ equivalent of 'text to columns' | Excel Programming |