英文:
Excel VBA: Obtain the Column and Row of current element in "For Each" loop
问题
我对VBA Excel宏相对陌生，可能有些地方理解错了。
我试图提取在For Each循环中正在循环的元素“rng”的范围（即“A1”，“A2”等），但我得到的是它的单元格值。
我假设VBA.Interaction.MsgBox prompt:=rng也会返回活动单元格的范围值，但实际上并不会。
我简化了代码，但意图是将符合某些标准的单元格（“A1”，“A2”等）的位置保存在数组中，以便进行进一步处理。
在For Each循环中，我如何提取rng的列和行索引？
谢谢。
英文:
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
答案1
得分: 2
MsgBox rng.Address(RowAbsolute:=False, ColumnAbsolute:=False)
英文:
MsgBox rng.Address(RowAbsolute:=False, ColumnAbsolute:=False )
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论