Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inconsistency in how Range object is accepted?
Why is it that this statement is acceptable:
With WorksheetsToFormat(N).Range("A2:G65536") But this one isn't? With WorksheetsToFormat(N).Range(Cells(2,1),Cells(65536 ,7)) When I try to use the second one, I get an error that the Range object failed. WorksheetsToFormat is declared as: Dim WorksheetsToFormat(1 to 8) as Worksheet |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inconsistency in how Range object is accepted?
Hi Chris,
This is a case of not fully-qualifying your range references. In your second example: With WorksheetsToFormat(N).Range(Cells(2,1),Cells(65536 ,7)) The Cells references are not qualified, so if the worksheet referred to by WorksheetsToFormat(N) is not the active worksheet (the one used by an unqualified range reference), you'll get a runtime error. I usually accomplish this by using a With block, but you're already using one. Maybe you could nest With blocks like this: With WorksheetsToFormat(N) With .Range(.Cells(2,1), .Cells(65536,7)) '/ your code End With End With -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Chris B wrote: Why is it that this statement is acceptable: With WorksheetsToFormat(N).Range("A2:G65536") But this one isn't? With WorksheetsToFormat(N).Range(Cells(2,1),Cells(65536 ,7)) When I try to use the second one, I get an error that the Range object failed. WorksheetsToFormat is declared as: Dim WorksheetsToFormat(1 to 8) as Worksheet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inconsistency in how Range object is accepted?
cells must be qualified as well
With WorksheetsToFormat(N) With .Range(.Cells(2,1),.Cells(65536,7)) End With End With or With WorksheetsToFormat(N).Range("A2").Resize(65535,7) -- Regards, Tom Ogilvy "Chris B" wrote in message ... Why is it that this statement is acceptable: With WorksheetsToFormat(N).Range("A2:G65536") But this one isn't? With WorksheetsToFormat(N).Range(Cells(2,1),Cells(65536 ,7)) When I try to use the second one, I get an error that the Range object failed. WorksheetsToFormat is declared as: Dim WorksheetsToFormat(1 to 8) as Worksheet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel inconsistency | Excel Discussion (Misc queries) | |||
returning pivottable object from a range object | Excel Programming | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming |