CODEWITHSUNDEEP
excelvbaforeach
Imach
Imach

Reputation: 3

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

I am fairly new to VBA Excel Macros and I probably misunderstood something.

I am trying to extract the range (as in "A1", "A2", etc) of the element "rng" that is looping in a For Each loop, but instead I am getting its cell value. I assumed VBA.Interaction.MsgBox prompt:=rng would return the range value of the active cell too, but not.

I simplified the code but the intention is to save in a array the location of cells ("A1", "A2", etc) matching a criteria for further processing.

How do I extract the column and row indexes of rng in a For Each loop?

Thanks

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

Upvotes: 0

Views: 450

Answers (1)

Anup Kumar
Anup Kumar

Reputation: 36

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

Upvotes: 2

Related Questions