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

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

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