VBA：VLookUp 多个结果

我需要一些代码方面的帮助。

我正在尝试执行 VLookup，并将数据显示在 O、P 和 Q 列中。

我想做的是循环遍历sheet("Global") A列，从第3行开始，直到最后使用的行。它需要匹配 A 列中从第 2 行开始的工作表（“详细信息”）中的数据。

因此，当它找到匹配值时，它将显示“全局”O2 中的“详细信息”C2、“全局”P2 中的“详细信息”I2 和“全局”Q2 中的“详细信息”G2 的结果。

然后它需要循环“全局”匹配并复制所有数据。如果未找到匹配项，则显示“NA!”。

我需要它做的最后一件事是删除 Global 中未找到匹配项的所有行。

我下面的代码满足了我的需要，唯一的问题是它非常慢，需要几分钟才能循环 800 行，有时甚至更长！

有没有另一种方法可以做到这一点，运行得更流畅、更快？

任何帮助表示赞赏！

谢谢

`Private Sub btnVlookUp_Click()
Dim i, j, lastG, lastD As Long

' find last row
lastG = Sheets("Global").Cells(Rows.Count, "B").End(xlUp).Row
lastD = Sheets("Details").Cells(Rows.Count, "A").End(xlUp).Row

' loop over values in "Global"
For i = 3 To lastG
    lookupVal = Sheets("Global").Cells(i, "B") ' value to find

    ' loop over values in "details"
    For j = 2 To lastD
        currVal = Sheets("Details").Cells(j, "A")

        If lookupVal = currVal Then
            Sheets("Global").Cells(i, "O") = Sheets("Details").Cells(j, "C")
            Sheets("Global").Cells(i, "P") = Sheets("Details").Cells(j, "I")
            Sheets("Global").Cells(i, "Q") = Sheets("Details").Cells(j, "G")
            ' mark the row
            Sheets("Details").Cells(j, "Z") = "marked"

        End If
    Next j
Next i

' loop over rows in "details" and delete rows which have not been marked
For j = 2 To lastD
    If Sheets("Details").Cells(j, "Z") <> "marked" Then
        ' delete unmarked rows
        Sheets("Details").Cells(j, "A").EntireRow.Delete
        If Sheets("Details").Cells(j, "B") <> "" Then
            j = j - 1 ' revert iterator so it doesn't skip rows
        End If
    Else:
        ' remove the mark
        Sheets("Details").Cells(j, "Z") = ""
    End If
Next j
End Sub`

根据这里的建议以及大量的试验和错误，我成功地调整了我的代码。

我对 600 多条记录进行了测试，它在几秒钟内运行，而在以前的代码中需要几分钟的时间。

如果您能看到执行以下代码的更好方法，请告诉我，我仍在学习 VBA，因此所有帮助我都可以得到更好的帮助！

感谢所有的支持！！！！！！！！

Private Sub btnVlookUp_Click()
Dim i, j, lastG, lastD As Long
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .CutCopyMode = False
End With
' find last row
lastG = Sheets("Global").Cells(Rows.Count, "B").End(xlUp).Row
lastD = Sheets("Details").Cells(Rows.Count, "A").End(xlUp).Row

' loop over values in "Global"
For i = 2 To lastG
    lookupVal = Sheets("Global").Cells(i, "B") ' value to find

    ' loop over values in "details"
    For j = 2 To lastD
        currVal = Sheets("Details").Cells(j, "A")

        If lookupVal = currVal Then
            Sheets("Global").Cells(i, "O") = Sheets("Details").Cells(j, "C")
            Sheets("Global").Cells(i, "P") = Sheets("Details").Cells(j, "I")
            Sheets("Global").Cells(i, "Q") = Sheets("Details").Cells(j, "G")
            ' mark the row
            Sheets("Details").Cells(j, "Z") = "marked"
            Sheets("Details").Cells(1, "Z") = "marked"
        Exit For
        End If
    Next j
Next i

On Error Resume Next
Sheets("Details").Columns("Z").SpecialCells(xlBlanks).EntireRow.Delete
Sheets("Details").Columns("Z").ClearContents

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .CutCopyMode = True
End With

End Sub