Simple Excel VBA Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
You might consider using a wrapper function for VLOOKUP instead - that
way it can be flexible enough to use with HLOOKUP, or MATCH, too:

For instance:

=IfNA(VLOOKUP(A1,J:K,2,FALSE),"NA VALUE")

or just

=IFNA(MATCH(A1,J:J,FALSE))


Here's one implementation:

Public Function IfNA(ByRef vTest As Variant, _
Optional ByRef vDefault As Variant = vbNullString) As Variant
If vTest = CVErr(xlErrNA) Then
IfNA = vDefault
Else
IfNA = vTest
End If
End Function
 
Hello,

In case of an error your function does not get further within the
isna() call, I think.

I suggest:
Function VLOOKUP_NA(VAL1, Range, OFFSET, TF, NAVALUE)

On Error Resume Next
VLOOKUP_NA = WorksheetFunction.VLookup(VAL1, Range, OFFSET, TF)
If Err.Number <> 0 Then VLOOKUP_NA = NAVALUE

End Function

Regards,
Bernd
 
My experience has been when invoking Vlookup in VBA with
WorksheetFunction.VLookup
I always get a run-time error "cannot get the vlookup property of the
worksheetfunction class" any time vlookup evaluates to NA.

But when I use Application.Vlookup, no run time errors.
 
When I use the WorksheetFunction.Find, the module always aborts
with "run-time error#1004 cannot get the Find property of the
worksheet function class". Using Application.Find works fine.

Is this true for all of the WorksheetFunction properties?

Dave
 
Thanks for pointing me to chip's web page - great info there.
I'm still pretty green at VBA programming, and this discussion
group is fantastic!

Dave
 
Back
Top