Vlookup在具有可变查找值的过滤范围内，可变查找范围VBA

Sub DynamicVLookup()
    Dim ws As Worksheet
    Dim lookupValue As Variant
    Dim lastRow As Long
    Dim i As Long
    Dim foundRow As Long
    
    ' 设置工作表和工作区域
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 假设查找值在 B1 单元格
    lookupValue = ws.Range("B1").Value
    
    ' 循环遍历过滤后的数据范围
    For i = 2 To lastRow ' 假设第一行是标题行
        ' 假设我们只处理列 A 中值大于 10 的行
        If ws.Cells(i, "A").Value > 10 Then
            ' 使用 VLOOKUP 查找并返回值
            foundRow = Application.WorksheetFunction.Match(lookupValue, ws.Range("A2:A" & lastRow), 0)
            If Not IsError(foundRow) Then
                ' 假设我们要返回的值在列 C
                ws.Cells(i, "C").Value = Application.WorksheetFunction.VLookup(lookupValue, ws.Range("A2:C" & lastRow), 3, False)
            End If
        End If
    Next i
End Sub