VBA VLookUp 运行时错误

Question

我正在同一个工作表上执行循环 vlookup，但出现运行时错误:1004:无法使用以下代码获取 WorksheetFunction 类的 Vlookup 属性:

Sub Test()        
    Dim d As Long 
    Dim lastrowx2 As Long 
    lastrowx2 = Worksheets("WS").Cells(Rows.Count, 1).End(xlUp).Row

    For d = 2 To lastrowx2

        Worksheets("WS").Cells(d, 16).Value = _ 'cell where the lookup value will be placed
        Application.WorksheetFunction.VLookup( _ 'vlookup function
        Worksheets("WS").Cells(d, 15).Value, _ 'value to look for
        Worksheets("WS").Range("X:Y"), 2, 0) 'range and column to look    
    Next

End Sub

我在这里做错了吗？

Answer 1

尽管我删除了代码中的注释，但该代码确实对我有用。我唯一能看到的是你可能必须使用 iferror 语句以防万一没有找到。

Sub Test()

    Dim d As Long
    Dim lastrowx2 As Long, x, sh As Worksheet
    Set sh = Sheets("WS")

    With sh
        lastrowx2 = .Cells(.Rows.Count, 15).End(xlUp).Row

        For d = 2 To lastrowx2
            x = Application.VLookup(.Cells(d, 15).Value, .Range("X:Y"), 2, False)
            If IsError(x) Then x = "Not Found"
            Range("P" & d).Value = x
        Next d
    End With

End Sub