Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range (newbie)
ok, im very new, so im learning as i go, i keep getting error "Subscript out
of range" when i use this, if i watch the sheets, it starts to do what it is supposed to do, then i get the error.. could someone please help me --- Sub CopyToSheets() Dim rng As Range Dim oCell As Range Set rng = Range("B4:B" & Cells(Rows.Count, "B").End(xlUp).Row) For Each oCell In rng oCell.EntireRow.Copy _ Destination:=Sheets(oCell.Value).Range("A65536").E nd(xlUp).Offset(1, 0) Next oCell End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range (newbie)
Again, you need to tell us on what line you get the error. Are you stepping
through the code with the F8 key in the VB Editor? You should be... On Sun, 6 Mar 2005 15:15:03 -0800, Michael A wrote: ok, im very new, so im learning as i go, i keep getting error "Subscript out of range" when i use this, if i watch the sheets, it starts to do what it is supposed to do, then i get the error.. could someone please help me --- Sub CopyToSheets() Dim rng As Range Dim oCell As Range Set rng = Range("B4:B" & Cells(Rows.Count, "B").End(xlUp).Row) For Each oCell In rng oCell.EntireRow.Copy _ Destination:=Sheets(oCell.Value).Range("A65536").E nd(xlUp).Offset(1, 0) Next oCell End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range (newbie)
I'd guess that you have at least one value in B4:B## that has a value that
doesn't correspond to a worksheet name. Maybe you have a typo or extra spaces (leading/trailing/embedded) that makes your code fail. Michael A wrote: ok, im very new, so im learning as i go, i keep getting error "Subscript out of range" when i use this, if i watch the sheets, it starts to do what it is supposed to do, then i get the error.. could someone please help me --- Sub CopyToSheets() Dim rng As Range Dim oCell As Range Set rng = Range("B4:B" & Cells(Rows.Count, "B").End(xlUp).Row) For Each oCell In rng oCell.EntireRow.Copy _ Destination:=Sheets(oCell.Value).Range("A65536").E nd(xlUp).Offset(1, 0) Next oCell End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range (newbie)
Ok, i have the code working now, but its not working as intended. i dont get
any errors.. but its not moving all the data, only some of it, the names are correct, it just dosn't move them. For example, it skips everything on the "BOB" sheet, even though there is plenty of things that match that criteria, but the "RES" sheet has everythign move over just fine.. here is the code i have that produces no errors Sub CopyToSheets() Dim rng As Range Dim oCell As Range Set rng = Range("B4:B" & Cells(Rows.Count, "B").End(xlUp).Row) For Each oCell In rng oCell.EntireRow.Copy _ Destination:=Sheets(oCell.Value).Range("A65536").E nd(xlUp).Offset(1, 0) Next oCell End Sub "Dave Peterson" wrote: I'd guess that you have at least one value in B4:B## that has a value that doesn't correspond to a worksheet name. Maybe you have a typo or extra spaces (leading/trailing/embedded) that makes your code fail. Michael A wrote: ok, im very new, so im learning as i go, i keep getting error "Subscript out of range" when i use this, if i watch the sheets, it starts to do what it is supposed to do, then i get the error.. could someone please help me --- Sub CopyToSheets() Dim rng As Range Dim oCell As Range Set rng = Range("B4:B" & Cells(Rows.Count, "B").End(xlUp).Row) For Each oCell In rng oCell.EntireRow.Copy _ Destination:=Sheets(oCell.Value).Range("A65536").E nd(xlUp).Offset(1, 0) Next oCell End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range (newbie)
Any chance that column A is empty on any of those rows?
If it is, then: destination:=Sheets(oCell.Value).Range("A65536").E nd(xlUp).Offset(1, 0) is going to have trouble. You may be overwriting the same row again and again and again. Maybe you could use column B--since you know it's populated: destination:=Sheets(oCell.Value).Range("B65536").E nd(xlUp).Offset(1, -1) Michael A wrote: Ok, i have the code working now, but its not working as intended. i dont get any errors.. but its not moving all the data, only some of it, the names are correct, it just dosn't move them. For example, it skips everything on the "BOB" sheet, even though there is plenty of things that match that criteria, but the "RES" sheet has everythign move over just fine.. here is the code i have that produces no errors Sub CopyToSheets() Dim rng As Range Dim oCell As Range Set rng = Range("B4:B" & Cells(Rows.Count, "B").End(xlUp).Row) For Each oCell In rng oCell.EntireRow.Copy _ Destination:=Sheets(oCell.Value).Range("A65536").E nd(xlUp).Offset(1, 0) Next oCell End Sub "Dave Peterson" wrote: I'd guess that you have at least one value in B4:B## that has a value that doesn't correspond to a worksheet name. Maybe you have a typo or extra spaces (leading/trailing/embedded) that makes your code fail. Michael A wrote: ok, im very new, so im learning as i go, i keep getting error "Subscript out of range" when i use this, if i watch the sheets, it starts to do what it is supposed to do, then i get the error.. could someone please help me --- Sub CopyToSheets() Dim rng As Range Dim oCell As Range Set rng = Range("B4:B" & Cells(Rows.Count, "B").End(xlUp).Row) For Each oCell In rng oCell.EntireRow.Copy _ Destination:=Sheets(oCell.Value).Range("A65536").E nd(xlUp).Offset(1, 0) Next oCell End Sub -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript out of range | Excel Discussion (Misc queries) | |||
what does 'Subscript Out of range' mean?? | Excel Worksheet Functions | |||
Subscript out of Range | Excel Programming | |||
Subscript is out of Range | Excel Programming | |||
Subscript out of range | Excel Programming |