如何在循环显示一系列单元格时获取当前单元格编号（Excel VBA）

I'm trying to get the current cell number whilst looping through a range of cells.

我正在尝试获取当前的单元格数,同时循环遍历一系列单元格。

For Each i In Sheet3.Range("A3:A213")

  msgbox(Sheet3.Range("B"&currentcellnumberinloop).Value)

Next

The purpose of this is to retrieve a value from another cell in the same row e.g. A1 and B1.

这样做的目的是从同一行中的另一个单元格中检索值,例如A1和B1。

3 个解决方案

#1

The i is probably declared as Range object(or Variant). Therefore to get the row number and retrieve the value in neighboring B column you have to call the .Row method of the i object

i可能被声明为Range对象(或Variant)。因此,要获取行号并检索相邻B列中的值,您必须调用i对象的.Row方法

Sub ForEachAndFor()

    Dim i As Range

    For Each i In Sheet3.Range("A3:A213")
        MsgBox Sheet3.Range("B" & i.Row).Value
    Next

End Sub

You could also use the Offset(how_many_rows_up_or_down, how_many_columns_left_or_right)

您也可以使用Offset(how_many_rows_up_or_down,how_many_columns_left_or_right)

you indicate rows down with a positive number an rows up with a negative number

你用正数表示行向下,用负数表示行

same applies to the columns, use - to navigate to the left of the current cell, and positive number to the right of the current cell.

同样适用于列,使用 - 导航到当前单元格的左侧,以及当前单元格右侧的正数。

Sub ForEachSub()

    Dim i as Range

    For Each i in Sheet3.Range("A3:A213")
        MsgBox i.Offset(0, 1).Value
    next i 

End Sub

#2

Try this

Dim i as Integer

    For Each i In Sheet3.Range("A3:D213").Rows.Count
     msgbox(Sheet3.Range("B" & i).Value)
    Next i

#3

In your code variable i is of a Range type, thus you have to treat it that way. You can use Offset to get to what is in relation to your i address, like that:

在你的代码变量中,我是一个Range类型,因此你必须以这种方式对待它。您可以使用Offset来获取与您的i地址相关的内容,例如:

For Each i In Sheet3.Range("A3:A213")

  MsgBox (i.Offset(0, 1).Value)

Next i

#1