Excel VBA Obtain the Column and Row of current element in For Each loop

Question

Since I'm still learning how to use VBA Excel Macros, I'm sure I misread something.

I'm attempting to get the range of the element "rng" that is looping in a for each loop, like "A1", "A2", etc., but I'm just receiving the cell value. VBA, I supposed. Interaction. However, MsgBox prompt:=rng does not also return the range value of the current cell.

Although I streamlined the code, the goal is to preserve the locations of cells ("A1," "A2," etc.) that meet a criteria in an array for later processing.

In a For Each loop, how do I get the column and row indexes of the rng?

Sub test() Dim rng As Range

For Each rng In Range("A1:A10")
    rng.Value = rng.Value + 10
    VBA.Interaction.MsgBox prompt:=rng ' this gives me 10, 10,... instead of "A1", "A2"....
Next rng

End Sub

Answer 1

Try this:

MsgBox rng.Address(RowAbsolute:=False, ColumnAbsolute:=False )