Simple Excel VBA Question

[Michael Saathoff]

I am trying to write a simple Excel VBA Function and I am running into
trouble. The function is a simple Vlookup with an additional argument to
return if the Vlookup evaluates to #N/A.

If the Lookup function does not evaluate to #N/A the function works fine;
however, I get an error if it evaluates to #N/A.

Below is the code and I would greatly appreciate help.
Michael

Code:
Function VLOOKUP_NA(VAL1, RANGE, OFFSET, TF, NAVALUE)

If WorksheetFunction.IsNA(WorksheetFunction.VLookup(VAL1, RANGE, OFFSET,
TF)) = True Then
VLOOKUP_NA = NAVALUE
ElseIf WorksheetFunction.IsNA(WorksheetFunction.VLookup(VAL1, RANGE, OFFSET,
TF)) = False Then
VLOOKUP_NA = WorksheetFunction.VLookup(VAL1, RANGE, OFFSET, TF)
End If

End Function

 

[Andy Pope]

Hi,

If the lookup fails it does not return #N/A instead it raises the error;

1004, Unable to get the VLookup property of the WorksheetFunction class

Or it would if not being called from a worksheet. In this instance it
surpressed the error message and simple returns #VALUE.

Try this modification.

Function VLOOKUP_NA(VAL1, RANGE, OFFSET, TF, NAVALUE)

On Error GoTo Err_Vlookup_na
VLOOKUP_NA = WorksheetFunction.VLookup(VAL1, RANGE, OFFSET, TF)
Exit Function

Err_Vlookup_na:
VLOOKUP_NA = NAVALUE
Exit Function
End Function

Cheers
Andy