When iterating an Excel range, values write to every second cell

Question

I'm iterating through column "A" of each row in a range determined by .UsedRange(), and writing the row number to the cell. The strange behaviour I'm seeing is when the row number is written to the cell, the consecutive row number is written only to every second cell:

(1,1) = "1"

(2,1) = empty

(3,1) = "2"

(4,1) = empty

(5,1) = "3"

(6,1) = empty

(7,1) = "4"

...

The worksheet has 10 rows, with empty cells in column "A", and single words in every cell in both column "B" and "C". When I step through the code I can see the row count (oRow.Row) iterate consecutively as I'd expect, but the values are written elsewhere. I tried resetting .UsedRange() as mentioned here: http://www.j-walk.com/ss/excel/tips/tip73.htm but there was no change with the outcome.

The code I'm using is:

Option Explicit

Sub IterateAndWrite()
    Dim oRange As Range
    Dim oRow As Range
    Dim lCount As Long

    Set oRange = ThisWorkbook.Worksheets(1).UsedRange

    For Each oRow In oRange.Rows
        oRow.Cells(oRow.Row, 1).Value = oRow.Row
        ' Reset the range
        'lCount = ActiveWorkbook.Worksheets(1).UsedRange.Rows.Count
    Next oRow
End Sub

Where am I going wrong?

Answer 1

Try assigning the value in your loop using one of these methods, which avoid the need to use an index/counter variable.

For Each oRow In oRange.Rows 
    oRange.Cells(oRow.Row, 1).Value = oRow.Row
Next

Alternatively this should also work:

oRow.Cells(1,1).Value = oRow.Row

WHY?

Because oRow is a range object that represents a Row, e.g., $B$1:$C$1, etc. When you use the .Cells method, you're passing it a row argument (oRow.Row) that is outside of the oRow object/range.When used in this single-index way, it seems kind of equivalent to the Offset method.

For example, Range("B2").Cells(2,1) is the equivalent of Range("B2").Offset(1,0) which is the equivalent of Range("B3"), so, by using an row index that is outside the row itself, you're causing it to "skip" to the next row :)

There is some more information about this via Chip Pearson's website:

It is not necessary for the cell to be within the range in order to be referenced this way. ... What is not documented is that this method of referencing continues down the worksheet; e.g., Range("A1:B2")(5) refers to Cell A3, Range("A1:B2")(14) refers to Cell B7, etc.

Answer 2

Try:

Sub IterateAndWrite()
    Dim oRange As Range
    Dim oRow As Range
    Dim lCount As Long

    Set oRange = ThisWorkbook.Worksheets(1).UsedRange

    For lCount = 1 To oRange.Rows.Count
        oRange.Cells(lCount, 1).Value = oRange.Cells(lCount, 1).Row
    Next
End Sub

Answer 3

here is how i understand your concern:

Option Explicit

Sub IterateAndWrite()
    Dim oRange As Range
    Dim oRow, range_to_write As Range

    Set oRange = ThisWorkbook.Worksheets(1).UsedRange

    'you need to set the range where you want to write as in below
    Set range_to_write = oRange.Offset(, -1).Resize(, 1)

    'here instead of rows, use cells since you are to iterate cells
    For Each oRow In range_to_write.Cells 
        oRow.Value = oRow.Row 'oRow is now a range pertaining to a Cell
    Next oRow

End Sub

Hope this is what you need.