Unable to get the VLookup property

[Westbury]

Within a longer macro I've included the following


Dim readingdate As String
Dim yrstart As Date
yrstart = Application.WorksheetFunction.VLookup(readingdate, Sheets("Lookups").Range("B7:E19"), 4, False)

but it returns an error "Unable to get the VLookup propertry of the Worksheetfunction class".

The VLookup works as an spreadsheet function. What's stopping it working in VBA?

thanks
Geoff

 

[Leith Ross]

Hello Geoff,

The WorksheetFunction in VBA does not return the error code that occurs like Excel does. Generally the message you are seeing means you have an #N/A error. If readingdate is not the first column in the table array (B7:E19) of your VLookup this will cause the error.

If you use Application.VLookup and assign a Variant variable as the return value, you will get an error code that can be translated into the Excel error.

Dim Msg As string
Dim x As Variant
    x = Application.VLookup(readingdate, Sheets("Lookups").Range("B7:E19"), 4, False)
        Select Case x
            Case Is = "Error 2000": Msg = "#NULL!"
            Case Is = "Error 2007": Msgr = "#DIV/0!"
            Case Is = "Error 2015": Msg = "#VALUE!"
            Case Is = "Error 2023": Msg = "#REF!"
            Case Is = "Error 2029": Msg = "#NAME?"
            Case Is = "Error 2036": Msg = "NUM!"
            Case Is = "Error 2042": Msg = "#N/A"
        End Select
    If Msg <> "" Then MsgBox Msg

 

[Westbury]

Leith,

thanks for this post.

I've added the script and it returns a run time error 13 type mismatch and highlights the

Case Is = "Error 2000"

Geoff

 

[Leith Ross]

Hello Westbury,

Here is updated and tested macro code.

Sub TestIt()


    Dim Msg As String
    Dim x   As Variant
    
        x = Application.VLookup("Forecaster", Range("B2:E3"), 4, False)
        
        Select Case CLng(x)
            Case 2000: Msg = "#NULL!"
            Case 2007: Msgr = "#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
        
        If Msg <> "" Then MsgBox "Worksheet Function Error:" & vbLf & vbTab & Msg, vbExclamatioon, "Error"
        
End Sub

 

[Westbury]

I've run the updated code and it returned Worksheet function error #N/A.

What does this mean?

Geoff

 

[Leith Ross]

Hello Geoff,

The error is saying the value in readingdate is Not Available, i.e. this value was not found in the first column of your search range "B7:E19".