Excel VBA Question?

Question

I am trying to create a Vlookup function with an additional argument to
return if the vlookup function evaluates to #N/A (Below is the code).

It works fine unless the function evaluates to #N/A.

I would greatly appreciate any help,
Michael

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

If WorksheetFunction.IsNA(WorksheetFunction.VLookup(VAL1, Range, OFFSET,
TF)) <> False 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

Thanks for the quick response; however, I still can't get it to work. I modified the code (see below); however, it the function evaluates to #N/A it is still not returning the "NAVALUE".

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

VLOOKUP_NA = WorksheetFunction.VLookup(VAL1, Range, OFFSET, TF)

If WorksheetFunction.IsNA(VLOOKUP_NA) = True Then
VLOOKUP_NA = NAVALUE
End If

End Function

Answer 1

I see your problem. You need to use the function:

CVErr

You want an evaluation something like this:

If CVErr(WorksheetFunction.VLookup....) = _
CVErr(xlErrNA) Then ....

Now you're going to tell Excel what to do when it gets the #N/A error.

CVErr(xlErrNA) will give you the value of "Error 2042". If you evaluate the error of the Excel VLOOKUP function with CVErr and get Error 2042 then you know you have a matched error type for #N/A. Once you find that the number for the error type matches then you can tell the function what value to enter in the cell. You do that by stating:

VLOOKUP_NA = NAVALUE

as the first answerer stated.

If you need help, feel free to write me. Yahoo cuts off what you write on here. So I used the ... for a continuation. I think you can figure out what goes in the rest of the line.

Answer 2

Yahoo! Answers cuts off part of those three calling sequences (the critical parts, natch!), so I can't tell if all three calls to Worksheet Function.VLookup have the same parameters, but if they do, you're making two calls more than you need to. Instead, do this:

VLOOKUP_NA = WorksheetFunction.VLookup( yada, yada)
if ( WorksheetFunction.ISNA( VLOOKUP_NA )) ' = TRUE not needed
VLOOKUP_NA = NAVALUE
End If

And that's the entire function.

As to why the current test fails, I suspect it's because of your test for <> FALSE. Because of the way FALSE is represented inside the computer, testing for <> FALSE may not work the way you expect it to. Besides, since IsNA returns a TRUE or FALSE value, and the If..End If construct uses a TRUE or FALSE value, you don't have to test for equality or inequality, as my suggested code shows.

Hope that helps.