对于每个循环，都不按预期操作。

Question

我在excel中有两张单张，一张是带有数个单元格的板，另一张是引用(在前面的板中有数字)，我需要在单元格所在的引用中写同一行。

第一块板的图片，参考资料在哪里？

excel工作表的图像，我必须写出每个引用的位置。

我的vba代码

示例：

​

​

and 8 and是板，local.png是我编写单元格去定位的地方。

Option Explicit

Sub ciclo()

    Dim FindString As String
    Dim Rng As Range
    Dim matrixVal As Range

    Set matrixVal = Sheets("Localizações").Range("B1")
    FindString = matrixVal

    For Each Rng In matrixVal

        If Trim(FindString) <> "" Then

            With Sheets("Arm8").Range("A1:J10")

                Set Rng = .Find(What:=FindString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)

                If Not Rng Is Nothing Then
                    'Application.Goto Rng, False
                    'MsgBox Rng.Column & " - " & Rng.Row
                Else
                    MsgBox "Nothing found"
                End If

            End With

            With Sheets("Localizações")
                .Range("C1:C9").Value = Rng.Column
                .Range("D1:D9").Value = Rng.Row
            End With

        End If

    Next Rng

End Sub

我期望local.png中的输出是列C和D

2-9

2-7

2-8

2-4

5-4

7-4

5-9

9-7

9-0

Answer 1

第一，正如我在评论中所说：

Set matrixVal = Sheets("Localizações").Range("B1")

将matrixVal设置为一个单元格(准确地说，是B1)，因此您的For-Each循环除了这个单元格之外没有任何单元格要循环，所以它只能运行一次。

其次，需要在循环中更新FindString，否则您将一次又一次地搜索相同的值。

最后，您不应该更新循环中的Rng变量，因为您已经在使用它来遍历范围。您需要Range类型的第二个变量。

您的代码应该如下所示：

 Sub ciclo()

    Dim FindString As String
    Dim Rng As Range
    Dim cell As Range
    Dim matrixVal As Range

    Set matrixVal = ThisWorkbook.Worksheets("Localizacoes").Range("B1:B9")

    For Each cell In matrixVal
        FindString = cell.Value

        If Trim(FindString) <> "" Then

            With ThisWorkbook.Worksheets("Arm8").Range("A1:J10")

                Set Rng = .Find(What:=FindString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)

                If Not Rng Is Nothing Then
                    With ThisWorkbook.Worksheets("Localizacoes")
                        .Cells(cell.Row, "C").Value = Rng.Column
                        .Cells(cell.Row, "D").Value = Rng.Row
                    End With
                Else
                    MsgBox "Nothing found"
                End If

            End With

        End If

    Next cell

End Sub