VBA VLookUp Runtime Error

Question

I am doing a looping vlookup on the same worksheet but I am getting Runtime Error: 1004: Unable to get the Vlookup property of the WorksheetFunction class with this code:

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

Am I doing something wrong here?

Answer 1

The code does work for me, although I removed the comments in the code. The only thing I can see is you may have to use an iferror statement in case nothing is found.

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

Answer 2

In general, Application.VLookup is a bit friendlier than WorksheetFunction.VlookUp. The latter would throw a run-time error if it is not found, while the first writes Excel error in the cell. Thus, it is a good idea to start with a really simple data and then try to make it work. Let's assume you have this:

The idea is to write the values of column B to column A, from LookUp range "D:E". Or on range("A1") it should be 600, Range("B1") 500, etc.

This is the minimal code:

Sub TestMe()

    Dim curRow As Long
    Dim lastRow As Long

    lastRow = Worksheets(1).Cells(Rows.Count, "B").End(xlUp).Row
    Range("A:A").Clear

    For curRow = 1 To lastRow
        Cells(curRow, "A") = Application.VLookup(Cells(curRow, "B"), Range("D:E"), 2, 0)
    Next

End Sub

This is how it looks once you run it:

Following this model, you can build up further your code.