CODEWITHSUNDEEP
excelrangecellsvba
AHB0378
AHB0378

Reputation: 21

Excel VBA: Unexpected result with Range and Cells

I'm using Excel 2013. In my code I refer I have to manipulate typical data in an Excel Sheet. For instance: 

    **Names**  
       Ann  
       Jim  
       Tom  
       Liz

I don't want to include a +1 in my code when refering to specific cells within my data set.

I.e., to get "Ann" I would rather use 

Cells(1,1)

than

Cells (1+1,1)

So I define a Range excluding the headers: 

Set R = Range(Cells(2,1),Cells(5,1)

And now this statement refers to "Ann"

R.Cells(1,1)

Next I want to refer to a "subrange" within my range R:

    R.Range(R.Cells(2,1), R.Cells(3,1))

But surprisingly, now, instead of the expected "Jim" and "Tom" (second to third cells in my range R), I get "Ann" and "Jim" (second to third in the Excel sheet).

Can anyone provide some insight about what I am missing here?

Thanks

Upvotes: 2

Views: 69

Answers (1)

user4039065
user4039065

Reputation:

This is a classic case of not passing along inherited parentage. The Cells within R.Range(Cells(2,1), Cells(3,1)) have no idea that they belong to R so they default to the ActiveSheet property which may or may not even be the worksheet that R is on (more problems there with a Subscript out of range error).

with R
    debug.print .Range(.Cells(2,1), .Cells(3,1)).address
end with

The With ... End With statement is the easiest way to pass along inherited parentage. Note that .Cells and not Cells is used to pass on the reference. It could also be written as,

    debug.print R.Range(R.Cells(2,1), R.Cells(3,1)).address

Sample sub:

Sub Unexpected_result_with_Range_and_Cells()
    Dim R As Range

    Set R = Range(Cells(2, 1), Cells(5, 1))
    Debug.Print R.Address
    Debug.Print R.Cells(1, 1).Value

    With R
        Debug.Print .Range(.Cells(2, 1), .Cells(3, 1)).Address
        Debug.Print .Cells(2, 1).Value
        Debug.Print .Cells(3, 1).Value
    End With

End Sub

Results from the VBE's Immediate window:

$A$2:$A$5
Ann
$A$4:$A$5
Jim
Tom

Upvotes: 1

Related Questions