Error using Application.WorksheetFunction.VLookup

[mcomp72]

This is my first time trying to use VLOOKUP within VBA code, and I am not having any luck. I am getting a 1004 error: Unable to get the Vlookup property of the WorksheetFunction class.
I even found a similar question on this board where a user suggested adding some code to determine the specific error, but that is not working.

Here is my code:

Function GetSheetOfLineNumber(LineNum As String) As String

Dim MyVar As Variant
Dim Rng As Range

'***This variable is for testing only
Dim Msg As String

Set Rng = ThisWorkbook.Sheets("LineNumbers").Range("D1:G379")

MyVar = Application.WorksheetFunction.VLookup(LineNum, Rng, 4, False)

'***This Select block is for testing only
Select Case CLng(MyVar)
    Case 2000: Msg = "#NULL!"
    Case 2007: Msg = "#DIV/0!"
    Case 2015: Msg = "#VALUE!"
    Case 2023: Msg = "#REF!"
    Case 2029: Msg = "#NAME?"
    Case 2036: Msg = "NUM!"
    Case 2042: Msg = "#N/A"
End Select

'***This is for testing only
If Msg <> "" Then MsgBox "Worksheet Function Error:" & vbLf & vbTab & Msg, vbExclamation, "Error"

Set Rng = Nothing

GetSheetOfLineNumber = MyVar

End Function

The error is occurring on the Vlookup line, so it never gets to the Select statement to check the error.

The variable LineNum during my testing is equal to "277".

In Column D on the "LineNumbers" sheet, all cells contain formulas. However, the result of one of the formulas is definitely 277. (The formula is simply grabbing the value of another cell on a different sheet.) All cells in Column D are formatted as "General". Could that cause the error? (While 277 is the result of one of the cells, it's possible the result might not be numeric, which is why I am treating it as a String.)

I'm also not sure why the code above isn't working to at least show me the specific error. Any help would be greatly appreciated!

 

[Trebor76]

Hi mcomp72,

The crux of the problem is that LineNum is a string variable but it should be a numeric variable.

This works for me:

Option Explicit
Function GetSheetOfLineNumber(LineNum As Long) As String

On Error GoTo ErrTrap
    
    Dim Rng As Range
    
    Set Rng = ThisWorkbook.Sheets("LineNumbers").Range("D1:G379")
    GetSheetOfLineNumber = Application.WorksheetFunction.VLookup(LineNum, Rng, 4, False)
    Set Rng = Nothing
    
Exit Function

ErrTrap:

    Set Rng = Nothing
    MsgBox "Worksheet Function Error:" & vbNewLine & "Error Number: " & Err.Number & vbNewLine & "Error Description: " & Err.Description, vbExclamation, "Error"
    
End Function
Sub TestFn()

    MsgBox GetSheetOfLineNumber(1)

End Sub

Regards,

Robert

 

[mcomp72]

Hi mcomp72,

The crux of the problem is that LineNum is a string variable but it should be a numeric variable.

This works for me:

Option Explicit
Function GetSheetOfLineNumber(LineNum As Long) As String

On Error GoTo ErrTrap
   
    Dim Rng As Range
   
    Set Rng = ThisWorkbook.Sheets("LineNumbers").Range("D1:G379")
    GetSheetOfLineNumber = Application.WorksheetFunction.VLookup(LineNum, Rng, 4, False)
    Set Rng = Nothing
   
Exit Function

ErrTrap:

    Set Rng = Nothing
    MsgBox "Worksheet Function Error:" & vbNewLine & "Error Number: " & Err.Number & vbNewLine & "Error Description: " & Err.Description, vbExclamation, "Error"
   
End Function
Sub TestFn()

    MsgBox GetSheetOfLineNumber(1)

End Sub

Regards,

Robert

Thanks for this. One potential issue I see with it is that sometimes LineNum might be something like this: "A315"

Obviously that's not a numeric value. Any suggestion on how I could change the code so that it would still work if LineNum was a non-numeric value?

 

[jasonb75]

Best guess without seeing the data is that there is no exact match. You haven't used On Error Resume Next before the lookup line so your select case test will still fail.

 

[Trebor76]

One potential issue I see with it is that sometimes LineNum might be something like this: "A315"

Try this slightly amended solution:

Option Explicit
Function GetSheetOfLineNumber(LineNum As String) As String

On Error GoTo ErrTrap
    
    Dim Rng As Range
    
    Set Rng = ThisWorkbook.Sheets("LineNumbers").Range("D1:G379")
    GetSheetOfLineNumber = Application.WorksheetFunction.VLookup(IIf(IsNumeric(LineNum) = True, Val(LineNum), LineNum), Rng, 4, False)
    Set Rng = Nothing
    
Exit Function

ErrTrap:

    Set Rng = Nothing
    MsgBox "Worksheet Function Error:" & vbNewLine & "Error Number: " & Err.Number & vbNewLine & "Error Description: " & Err.Description, vbExclamation, "Error"
    
End Function
Sub TestFn()

    MsgBox GetSheetOfLineNumber(1)

End Sub

 

[mcomp72]

Try this slightly amended solution:

That worked. Thanks so much!!

 

[Trebor76]

That worked. Thanks so much!!

You're welcome

 

[jasonb75]

That function will only work if it is a certainty that the data in the lookup range (Rng) will not change. As Rng is not a precedent in the function there is nothing to trigger calculation unless you make it volatile.

Looking at the function, the solution would be to scrap the UDF attempts and use the lookup formula in the cell instead. More efficient and less error prone.

 

[mcomp72]

That function will only work if it is a certainty that the data in the lookup range (Rng) will not change. As Rng is not a precedent in the function there is nothing to trigger calculation unless you make it volatile.

Looking at the function, the solution would be to scrap the UDF attempts and use the lookup formula in the cell instead. More efficient and less error prone.

The function is never called directly from a worksheet, only via VBA code. In that instance, is it okay as is? Or would you still recommend scrapping it?

 

[jasonb75]

In that instance it should be ok but using vba range find might be better depending on the main task.