Menu
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.
821
Click the cell where you want to enter a reference to another cell. Type an equals (=) sign in the cell. Click the cell in the same worksheet you want to make a reference to, and the cell name is automatically entered after the equal sign. Press Enter to create the cell reference.
The Excel ADDRESS function returns the address for a cell based on a given row and column number. For example, =ADDRESS(1,1) returns $A$1.
=VLOOKUP("excel",A2:B8,2, FALSE) Type this formula in a blank cell and then press Enter key to apply it. The adjacent cell value of product “excel” in Column Product is returned in Cell D1.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
