VBA vlookup and ISNA or ISerror - run-time error '1004': unable to get the Vlookup property of the WorksheetFunction class RRS feed

  • Question

  • Hello,

    I'm trying to use a vlookup macro. The macro works fine when the vlookup finds something but when the result would be #n/a in the vlookup formula, I get the following message

    "run-time error '1004': unable to get the Vlookup property of the WorksheetFunction class"

    I've tried to use isna and iserror but do not seem to be doing it properly...

    Original VBA statement

    Sub tst_lookup()

     msgbox ( Application.WorksheetFunction.VLookup(ActiveSheet.Cells(3, 1).Value, Sheets(ActiveSheet.Cells(1, 2).Value).Range("A1:A200"), 1, False) )

    End Sub

    I've tried to use the If...then with ISNA / ISERROR to cater for the situations where the value cannot be found in the target sheet, but that didn't work

    If Application.WorksheetFunction.IsNA(Application.WorksheetFunction.VLookup(ActiveSheet.Cells(3, 1).Value, Sheets(ActiveSheet.Cells(1, 2).Value).Range("A1:A200"), 1, False)) Then MsgBox ("no data")

    Anyone got any idea?


    Friday, August 14, 2015 12:24 PM

All replies

  • Re:  lookup errors

    VBA returns two different types of errors when using worksheet functions in VBA.
    The type of error returned depends on the syntax of the code used.

    Application.VLookup returns a Variant error type that can be trapped by using the VBA "IsError" function.

    Application.WorksheetFunction.VLookup returns a VBA error that can be trapped with standard error handling...
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 7:57 PM
    Friday, August 14, 2015 7:27 PM