Rectangle 27 1

excel Difference between two ways of accessing a row's cells?


Cells(aRow.Row, 2).Address
Option Explicit 'in the first line of your modules: Ensures you need to declare all variables

Sub test6()
    Dim wRange As Range, aRow As Variant
    Set wRange = Range("B3:P10")

    For Each aRow In wRange.Rows
                                                ' Outputs of the first loop run:
        Debug.Print aRow.Address                ' $B$3:$P$3
        Debug.Print aRow.Cells(1, 2).Address    ' $C$3
        Debug.Print Cells(aRow.Row, 2).Address  ' $B$3
    Next
End Sub
aRow.EntireRow.Cells(1, 2).Address

Cells(aRow.Row, 2) is exactly the same as writing ActiveSheet.Cells(aRow.Row, 2) and refers to column 2 relative to the ActiveSheet which is B because the range of the sheet is starting at A.

ExplanationaRow is a sub range ($B$3:$P$3) out of the original range B3:P10 containing only one row (but not an entire row of the worksheet as you assume) so aRow.Cells(1, 2) refers to column 2 relative to aRow which is C because the range is starting with B not with A.

I changed your procedure a bit and added the output of Debug.Print as comment to better explain why this is an expected result:

Side note: I recommend not to assume the worksheet and fully qualify your cells/ranges so you always see to which range the cells are relative to.

Note