Setting the Range object with Cells on a different Sheet

Question

I have been creating Ranges with Cells for a while but always on the selected worksheet. I use the following syntax:

Dim r as Range
Set r = Range(Cells(1, 1), Cells(4, 2))

This has always worked.

I am not trying to select a range on a different sheet using the following syntax:

Dim r as Range
Set r = Worksheets("working").Range(Cells(1, 1), Cells(4, 2))

and get the following Error: "Application-defined or Object-defined error".

In the immediate Window, I am finding something very interesting.. assuming "r" has been dimmed and that the selected worksheet is (1) (not "working".. which is (2))

r = Worksheets("working").Range("A1:D2")   <<< THIS WORKS
r = Worksheets("working").Range(Cells(1, 1), Cells(4, 2))   << ERROR
r = Range(Cells(1, 1), Cells(4, 2)) <<< THIS WORKS (on selected sheet).

So I'm just confused and have been combing thru SO and other sites. I know I'm doing something obviously wrong... can someone help?

Thanks.

https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 — Tim Williams, Aug 22 '19 at 02:41
Thanks for the link... I'm happy urdearboy gave the example below since the "duplicate" did not give examples. — Ed Landau, Aug 22 '19 at 03:15

urdearboy · Accepted Answer · 2019-08-22T02:55:24.957

1

Qualify all objects (Including Cells).

Dim ws as Worksheet: Set ws = ThisWorkbook.Sheets("working")

Set r = ws.Range(ws.Cells(1, 1), ws.Cells(4, 2))

edited Aug 22 '19 at 02:55

answered Aug 22 '19 at 02:43

urdearboy

14,439
5
28
58

Thank you! But... that does not seem to work either. r = ws.Range("A1:D2") where Set ws = Worksheets("working"), gives me the same error – Ed Landau Aug 22 '19 at 03:18
Set workingRange = ws.Range("A1:B4") WORKS. – Ed Landau Aug 22 '19 at 03:20
Set workingRange = ws.Range(ws.Cells(0, 0), ws.Cells(4, rowNumber)) DOES NOT. – Ed Landau Aug 22 '19 at 03:21
2

There's no `Cells(0, 0)` - row/column indexes begin at 1 – Tim Williams Aug 22 '19 at 05:00

Setting the Range object with Cells on a different Sheet

1 Answers1

Linked