Why does Range.Address () return the address of the first cell when riveted?

Question

I came across some unexpected behavior when I wrote the answer for this question .

When combining calls, the Range Address always returns the address of the very first Range object in the instruction. For instance:

 Public Sub Unexpected() Debug.Print Range("B3").Address Debug.Print Range("B3").Range("A1").Address End Sub 

Returns the following output.

 $B$3 $B$3 

But I would expect it to return the address of the last Range object in the chain.

 $B$3 $A$1 

Can anyone explain this behavior? Preferably with a quote and a link to the relevant documentation.

Answer 1

There is documentation on using Range().Cells() , which indicates that Cells() will return a location within a given Range() relative to the top left cell of Range() . Testing this theory with Range().Range() gives:

 Public Sub Unexpected() Debug.Print Range("B1:C3").Range("A1").Address '$B$1 Debug.Print Range("B1:C3").Range("B1").Address '$C$1 End Sub 

The documentation is here :

When applied to a Range object, this property refers to a Range object.

For example, if the selection is cell C3 , then Selection.Range("B1") returns cell D3 because its relation to the Range object is returned by the Selection property. ActiveSheet.Range("B1") code, on the other hand, always returns cell B1 .