Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range error
This code returns a "Subscript out of range error at the marked line (it's
1 line in the code but wrapped in this post). The "copy" method works because the correct range is copied but the error occurs on the line with the paste method: ======================= Sub ProcessData() For Each rt In Worksheets("Routes").Range("A2", Worksheets("Routes").Range("A2").End(xlDown)) With Worksheets.Add .Name = rt End With For Each rcd In Worksheets("Data").Range("A2", Worksheets("Data").Range("A2").End(xlDown)) If rcd.Value = rt.Value Then Range(rcd, rcd.End(xlToRight)).Copy Worksheets(rt).Worksheets(rt).Range(Range("A1").En d(xlDown).Offset(1, 0), Range("A1").End(xlDown).Offset(1, 9)).Paste '<<<---- problem line Else End If Next rcd Next rt End Sub =========================== I have also tried pasting to a 1 cell range [Worksheets(rt).Range("A1").End(xlDown).Offset(1, 0)] which also generates an error. The data which is copied is A2:I2 (1 row x 9 columns) Any ideas? So close... :/ cheers Damien |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range error
Damien, Do you have some rows already on worksheet(rt). Else go go down to the very last row,65 536, and paste one row below which is impossible. In that case, use Range("A65000").End(xlUp).Offset(1,0)). Please also note that you only have to specify the upper left cell of the destination range and that "Offset(1, 9)"does not resize the range. See ammended line Regards JY For Each rt In Worksheets("Routes").Range("A2", Worksheets("Routes").Range("A2").End(xlDown)) With Worksheets.Add .Name = rt End With For Each rcd In Worksheets("Data").Range("A2", Worksheets("Data").Range("A2").End(xlDown)) If rcd.Value = rt.Value Then Range(rcd, rcd.End(xlToRight)).Copy XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx Worksheets(rt).Range(Range("A1").End(xlDown).Offse t(1, 0), Range("A650001").End(xlUp).Offset(1,0)).Paste ' xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxx Else End If Next rcd Next rt Regards JY "Damien McBain" wrote in message ... This code returns a "Subscript out of range error at the marked line (it's 1 line in the code but wrapped in this post). The "copy" method works because the correct range is copied but the error occurs on the line with the paste method: ======================= Sub ProcessData() For Each rt In Worksheets("Routes").Range("A2", Worksheets("Routes").Range("A2").End(xlDown)) With Worksheets.Add .Name = rt End With For Each rcd In Worksheets("Data").Range("A2", Worksheets("Data").Range("A2").End(xlDown)) If rcd.Value = rt.Value Then Range(rcd, rcd.End(xlToRight)).Copy Worksheets(rt).Worksheets(rt).Range(Range("A1").En d(xlDown).Offset(1, 0), Range("A1").End(xlDown).Offset(1, 9)).Paste '<<<---- problem line Else End If Next rcd Next rt End Sub =========================== I have also tried pasting to a 1 cell range [Worksheets(rt).Range("A1").End(xlDown).Offset(1, 0)] which also generates an error. The data which is copied is A2:I2 (1 row x 9 columns) Any ideas? So close... :/ cheers Damien |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range error
Jean-Yves wrote:
Damien, Do you have some rows already on worksheet(rt). Else go go down to the very last row,65 536, and paste one row below which is impossible. In that case, use Range("A65000").End(xlUp).Offset(1,0)). Please also note that you only have to specify the upper left cell of the destination range and that "Offset(1, 9)"does not resize the range. See ammended line Regards JY Thanks JY & Don. I now have this (which still doesn't work!) It still produces the subscript out of range error on the "copy" line. ========================== Sub ProcessData() For Each rt In Worksheets("Routes").Range("A2", Worksheets("Routes").Range("A2").End(xlDown)) With Worksheets.Add .Name = rt .Range("A1") = "Route" End With For Each rcd In Worksheets("Data").Range("A2", Worksheets("Data").Range("A2").End(xlDown)) If rcd.Value = rt.Value Then Range(rcd, rcd.End(xlToRight)).Copy _ Destination:=Worksheets(rt).Range("A65536").End(xl Up).Offset(1, 0) Else End If Next rcd Next rt End Sub ======================= grrr... For Each rt In Worksheets("Routes").Range("A2", Worksheets("Routes").Range("A2").End(xlDown)) With Worksheets.Add .Name = rt End With For Each rcd In Worksheets("Data").Range("A2", Worksheets("Data").Range("A2").End(xlDown)) If rcd.Value = rt.Value Then Range(rcd, rcd.End(xlToRight)).Copy XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx Worksheets(rt).Range(Range("A1").End(xlDown).Offse t(1, 0), Range("A650001").End(xlUp).Offset(1,0)).Paste ' xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxx Else End If Next rcd Next rt Regards JY "Damien McBain" wrote in message ... This code returns a "Subscript out of range error at the marked line (it's 1 line in the code but wrapped in this post). The "copy" method works because the correct range is copied but the error occurs on the line with the paste method: ======================= Sub ProcessData() For Each rt In Worksheets("Routes").Range("A2", Worksheets("Routes").Range("A2").End(xlDown)) With Worksheets.Add .Name = rt End With For Each rcd In Worksheets("Data").Range("A2", Worksheets("Data").Range("A2").End(xlDown)) If rcd.Value = rt.Value Then Range(rcd, rcd.End(xlToRight)).Copy Worksheets(rt).Worksheets(rt).Range(Range("A1").En d(xlDown).Offset(1, 0), Range("A1").End(xlDown).Offset(1, 9)).Paste '<<<---- problem line Else End If Next rcd Next rt End Sub =========================== I have also tried pasting to a 1 cell range [Worksheets(rt).Range("A1").End(xlDown).Offset(1, 0)] which also generates an error. The data which is copied is A2:I2 (1 row x 9 columns) Any ideas? So close... :/ cheers Damien |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range error
Subscript out of range means that there's some sort of collection that doesn't
have element by the name/index you're using. In this line: Range(rcd, rcd.End(xlToRight)).Copy _ Destination:=Worksheets(rt).Range("A65536").End(xl Up).Offset(1, 0) The only thing I see that could cause that error is worksheets(rt). If rt is a number (say 1234), then worksheets(1234) means the 1234th worksheet from the left. You could use this to make sure you're using the name of the sheet--not the index: Range(rcd, rcd.End(xlToRight)).Copy _ Destination:=Worksheets(cstr(rt)).Range("A65536"). End(xlUp).Offset(1, 0) cstr(rt) (I'd use cstr(rt.value) myself) will convert that number to a string--so it would be like typing: worksheets("1234")..... And I'd make another change, too. I'd qualify the range to copy: worksheets("data").Range(rcd, rcd.End(xlToRight)).Copy _ Destination:=Worksheets(cstr(rt.value)).Range("A65 536").End(xlUp).Offset(1, 0) Damien McBain wrote: <<snipped I now have this (which still doesn't work!) It still produces the subscript out of range error on the "copy" line. ========================== Sub ProcessData() For Each rt In Worksheets("Routes").Range("A2", Worksheets("Routes").Range("A2").End(xlDown)) With Worksheets.Add .Name = rt .Range("A1") = "Route" End With For Each rcd In Worksheets("Data").Range("A2", Worksheets("Data").Range("A2").End(xlDown)) If rcd.Value = rt.Value Then Range(rcd, rcd.End(xlToRight)).Copy _ Destination:=Worksheets(rt).Range("A65536").End(xl Up).Offset(1, 0) Else End If Next rcd Next rt End Sub ======================= grrr... For Each rt In Worksheets("Routes").Range("A2", Worksheets("Routes").Range("A2").End(xlDown)) With Worksheets.Add .Name = rt End With For Each rcd In Worksheets("Data").Range("A2", Worksheets("Data").Range("A2").End(xlDown)) If rcd.Value = rt.Value Then Range(rcd, rcd.End(xlToRight)).Copy XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx Worksheets(rt).Range(Range("A1").End(xlDown).Offse t(1, 0), Range("A650001").End(xlUp).Offset(1,0)).Paste ' xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxx Else End If Next rcd Next rt Regards JY "Damien McBain" wrote in message ... This code returns a "Subscript out of range error at the marked line (it's 1 line in the code but wrapped in this post). The "copy" method works because the correct range is copied but the error occurs on the line with the paste method: ======================= Sub ProcessData() For Each rt In Worksheets("Routes").Range("A2", Worksheets("Routes").Range("A2").End(xlDown)) With Worksheets.Add .Name = rt End With For Each rcd In Worksheets("Data").Range("A2", Worksheets("Data").Range("A2").End(xlDown)) If rcd.Value = rt.Value Then Range(rcd, rcd.End(xlToRight)).Copy Worksheets(rt).Worksheets(rt).Range(Range("A1").En d(xlDown).Offset(1, 0), Range("A1").End(xlDown).Offset(1, 9)).Paste '<<<---- problem line Else End If Next rcd Next rt End Sub =========================== I have also tried pasting to a 1 cell range [Worksheets(rt).Range("A1").End(xlDown).Offset(1, 0)] which also generates an error. The data which is copied is A2:I2 (1 row x 9 columns) Any ideas? So close... :/ cheers Damien -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range error
On Mar 9, 9:41 am, Dave Peterson wrote:
Subscript out of range means that there's some sort of collection that doesn't have element by the name/index you're using. In this line: Range(rcd, rcd.End(xlToRight)).Copy _ Destination:=Worksheets(rt).Range("A65536").End(xl Up).Offset(1, 0) The only thing I see that could cause that error is worksheets(rt). If rt is a number (say 1234), then worksheets(1234) means the 1234th worksheet from the left. You could use this to make sure you're using the name of the sheet--not the index: Range(rcd, rcd.End(xlToRight)).Copy _ Destination:=Worksheets(cstr(rt)).Range("A65536"). End(xlUp).Offset(1, 0) cstr(rt) (I'd use cstr(rt.value) myself) will convert that number to a string--so it would be like typing: worksheets("1234")..... And I'd make another change, too. I'd qualify the range to copy: worksheets("data").Range(rcd, rcd.End(xlToRight)).Copy _ Destination:=Worksheets(cstr(rt.value)).Range("A65 536").End(xlUp).Offset(1, 0) Thanks Dave, that's perfect! And thanks to everyone else who has helped . cheers Damien |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript out of range error? | Excel Programming | |||
Runtime Error - Subscript out of range despite On Error statement | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Type Mismatch error & subscript out of range error | Excel Programming |