CODEWITHSUNDEEP
excelvba
Ayush
Ayush

Reputation: 42450

How to get address of adjacent cell?

I have the following vba code to find occurrences of "0" in a following column:

For Each c In Range("B:B")

        If c.Value = "0" Then
            MsgBox "0 found at " & (c.Address)
        End If
    Next c

How can I modify the code so that when it finds a "0" at say, B6, it displays C7? ie. it display the cell diagonally adjacent to the one where the "0" is found.

Upvotes: 11

Views: 47522

Answers (2)

Alex P
Alex P

Reputation: 12487

You can use Offset

MsgBox "0 found at " & c.Offset(1,1).Address

The Offset property is of the form Offset(row, column). Examples:

Range("B6").Offset(0,0) //refers to cell B6
Range("B6").Offset(1,0) //move one row down (B7)
Range("B6").Offset(0,1) //move one column to the right (C6)
Range("B6").Offset(-1,0) //move one row up (B5)
Range("B6").Offset(0,-1) //move one column to the left (A6)

Upvotes: 27

Fionnuala
Fionnuala

Reputation: 91366

How about:

        MsgBox "0 found at " & Cells(c.Row + 1, c.Column + 1)

Upvotes: 9

Related Questions