General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

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

Valid XHTML 1.1Valid CSSMade on a Macintosh

Quick Links

Not familiar with User Defined Functions (UDFs)? See David McRitchie's Getting Started With Macros

Where to put your code

About Event Macros

Sum or Count based on colors