Excel VBA - Set and Select a Range Problem

[TheRobster]

I have the following VBA code:

Sub user_range()

    Dim myRange As Range
    Set myRange = Sheets("EventTypes").Range(Cells(5, 5), Cells(10, 10))
    myRange.Select

End Sub

Which works fine when the worksheet EventTypes is selected. However when I run the macro with any other sheet selected I get the error message "Run-time error '1004': Application-defined or object defined error". The error is due to the Set myRange line.

Surely it must be possible to define a range whilst not on the worksheet that the range will be on, and then select that range?

Thanks
-Rob

[vbfbryce]

Specify the workbook which contains that sheet when you set the range.

[TnTinMN]

Your issue is due to the use of the unqualified Cells(x,y) references. When you enter an unqualified reference, and it works, it is working because it is defaulting to a referenced on the Application object. With that stated, your code:

Set myRange = Sheets("EventTypes").Range(Cells(5, 5), Cells(10, 10))

is actually

Set myRange = Application.Sheets("EventTypes").Range(Application.Cells(5, 5), Application.Cells(10, 10))

However, Application.Cells represents the cells on the Application.ActiveWorkSheet object. So your code is really:

Set myRange = Application.ActiveWorkBook.Sheets("EventTypes").Range(Application.ActiveWorkSheet.Cells(5, 5), Application.ActiveWorkSheet.Cells(10, 10))

So you are telling it to return a Range from EventTypes that consists of a range from the ActiveSheet. That works when the active sheet is EventTypes, but makes no sense if the active worksheet is not EventTypes.

You will need construct the cell references to be independent of ActiveWorkSheet.