IsError & VLookup in VBA statement

[jfarc]

I have this formula in Cell F2 and it works fine:

IF(ISERROR(VLOOKUP(A2,INV,1,0))=FALSE,"D","")

I am trying to replicate the above formula as a VBA command line and when it tries to execute the 'IsError...' line I am getting an Error #1004 - Unable to get the VLookup property of the WorksheetFunction class.

        iFound = WorksheetFunction.IsError(WorksheetFunction.VLookup(Range("A2"), Range("INV"), 1, 0))
        If iFound = False Then Range("F2").Value = "D"

What am I doing wrong?

 

[VoG]

Try

iFound = IsError(Application.VLookup(Range("A2").Value, Range("INV"), 1, 0))

 

[jfarc]

Ok, that worked!

But, I also had the following line of code that worked fine:

  CUST = WorksheetFunction.VLookup(Range("D7"), Range("CCODES"), 1, 0)

When do you determine to use the 'Application.' vs. the 'WorksheetFunction.' and when not to use them at all?

 

[VoG]

You should use Application when there is the possibility of an error (e.g. using VLOOKUP or MATCH).

There is a discussion here http://www.dailydoseofexcel.com/archives/2004/09/24/the-worksheetfunction-method/

 

[jfarc]

Oh, didn't know that. Thanks!