1

When setting a range, and the active worksheet is not = to the reference I get an

Application Defined Object Defined Error

using this method below:

Set Rng = Worksheets("Worksheet2").Range(Cells(2,2),Cells(2,3))

But why does it work when I use this method:

Set Rng = Range(Worksheets("Worksheet2").Cells(2,2),Worksheets("Worksheet2").Cells(2,3))

Just trying to understand why exactly the last method works, but the first does not.

braX
  • 11,506
  • 5
  • 20
  • 33
user4467707
  • 75
  • 1
  • 8
  • 4
    Cells() in your first example refers to the activesheet, which might not be Worksheet2. https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 – Tim Williams Nov 13 '17 at 16:01

2 Answers2

2

You are breaking the range, as far as both Cells and Range should be refered to the same Parent. If you do not refer any of them, their parent is the activesheet:

Thus either refer the parent explicitly for both of them:

With Worksheets("worksheets2")
    Set Rng = .Range(.Cells(2, 2), .Cells(2, 3))
End With

or leave it empty for both of them (thus it would be the ActiveSheet):

Set Rng = Range(Cells(2,2),Cells(2,3))

To make this work Set Rng = Worksheets("Worksheet2").Range(Cells(2,2),Cells(2,3)), you have to make sure that Worksheets2 is your activesheet.

Vityata
  • 42,633
  • 8
  • 55
  • 100
0

Because you cannot have two different references for the worksheet and the cells. So it whould be like

Set Rng = Worksheets("Worksheet2").Range(Worksheets("Worksheet2").Cells(2, 2), Worksheets("Worksheet2").Cells(2, 3))
Harsh Dubey
  • 121
  • 4