This site will look better if you upgrade to a browser that supports web standards.
XL/VBA's Cells() property's default parent object is the Application. So unless it's qualified by explicitly including a worksheet or range parent object, Cells() refers to the ActiveSheet. So
Set rMyRange = Sheets("Sheet2").Range(Cells(1, 1), Cells(10, 10))
is equivalent to
Set rMyRange = Sheets("Sheet2").Range(ActiveSheet.Cells(1, 1), _ ActiveSheet.Cells(10, 10))
Therefore, if Sheet2 is not active, you'll get a run-time error ("Run-time error '1004':Application-defined or object-defined error"), since ranges can only exist on a single sheet. Instead you should explicitly qualify Cells()::
Set rMyRange = Sheets("Sheet2").Range(Sheets("Sheet2").Cells(2, 2), _ Sheets("Sheet2").Cells(11, 11))
or, more efficiently:
With Sheets("Sheet2") Set rMyRange = .Range(.Cells(2, 2), .Cells(11, 11)) End With
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.