This site will look better if you upgrade to a browser that supports web standards.

Unqualified Cells()

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 Saturday, 28 May 2005

McGimpsey & Associates

Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

Unqualified Cells()

Quick Links