Cell Address in a loop

Question

I am looping through a range of cells to check if a value is 0. My logic is if cell value is zero, then cell value is the previous cells value. If that previous cell is also zero, then it is the next cell's value. But I said what if the last cell or first cell is zero? I need to check that too because if it is the first cell or last, then the loop fails. My question is, what should the code be to pass through the last row as an address. I know the last row, but I do not know how to write it as an address with a known column. The data starts at row 2 and then goes to row X.

For each Cell In rng
    If Cell.Address="A2" Then
        If Cell.Value=0 Then
           Cell.Value=Cell.Offset(1,0).Value
        End if

     Elseif Cell.Address="AX" Then 'X is the last row
        If Cell.Value=0 Then
           Cell.Value=Cell.Offset(-1,0).Value
        End If

    Elseif Cell.Value=0 and Cell.Offset(1,0).Value=0 Then 
        Cell.Value=Cell.Offset(-1,0).Value

    Elseif Cell.Value=0 Then
        Cell.Value=Cell.Offset(1,0).Value

    Else
        Do Nothing

    End If
Next

Possible duplicate of [Error in finding last used cell in VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) — danieltakeshi, Nov 27 '17 at 16:15
How is `RANGE` defined? If `RANGE`is really the (badly named) variable then `RANGE(RANGE.Rows.Count, RANGE.Columns.Count).Address` will give you the last cell in that range. — Darren Bartrup-Cook, Nov 27 '17 at 16:16
Clarified the question. RANGE is a range, my actual name is different. Changed RANGE to rng — Jack Armstrong, Nov 27 '17 at 16:18
My comment still stands with `rng` - `rng(rng.cells.count).address` or `rng(1).address` is another way to return the address of the first and last cells. If you want the column you could use `rng(1).Column` — Darren Bartrup-Cook, Nov 27 '17 at 16:23

Darren Bartrup-Cook · Accepted Answer · 2017-11-27T16:53:33.980

I've added three rows to define the variables and range.
Other than that I've only made changes to the IF statement and the first ELSEIF statement.

Sub Test()

    Dim rng As Range
    Dim Cell As Range

    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:A20")

    For Each Cell In rng
        If Cell.Address = rng(1).Address Then
            If Cell.Value = 0 Then
               Cell.Value = Cell.Offset(1, 0).Value
            End If

         ElseIf Cell.Address = rng(rng.Cells.Count).Address Then
            If Cell.Value = 0 Then
               Cell.Value = Cell.Offset(-1, 0).Value
            End If

        ElseIf Cell.Value = 0 And Cell.Offset(1, 0).Value = 0 Then
            Cell.Value = Cell.Offset(-1, 0).Value

        ElseIf Cell.Value = 0 Then
            Cell.Value = Cell.Offset(1, 0).Value

        Else
            'Do Nothing

        End If
    Next

End Sub

Edit: (after answer accepted).

To loop through each cell in each column you'll need a loop to look at each column and then another to look at each cell within the column.

In the code below I have defined col as a range.
This is then used in the first loop (For Each col in rng.Columns).
The second loop then looks at each cell within col (For Each Cell in col.Cells).

Sub Test()

    Dim rng As Range
    Dim Cell As Range
    Dim col As Range

    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:Z20")

    For Each col In rng.Columns
        For Each Cell In col.Cells
            If Cell.Address = rng(1).Address Then
                If Cell.Value = 0 Then
                   Cell.Value = Cell.Offset(1, 0).Value
                End If

             ElseIf Cell.Address = rng(rng.Cells.Count).Address Then
                If Cell.Value = 0 Then
                   Cell.Value = Cell.Offset(-1, 0).Value
                End If

            ElseIf Cell.Value = 0 And Cell.Offset(1, 0).Value = 0 Then
                Cell.Value = Cell.Offset(-1, 0).Value

            ElseIf Cell.Value = 0 Then
                Cell.Value = Cell.Offset(1, 0).Value

            Else
                'Do Nothing

            End If
        Next
    Next col

End Sub

What if my range extends over multiple columns? I thought I would get an answer that would be similar to how I defined my range in the question. If not, then each column will be a range and I can have multiple loops. — Jack Armstrong, Nov 27 '17 at 16:41
If the `rng` is set to `A1:Z20` then `rng(rng.Cells.Count).Address` will return `Z20`. I'll add another section to my answer regarding looking at each column in turn. — Darren Bartrup-Cook, Nov 27 '17 at 16:49

Cell Address in a loop

1 Answers1

Linked