Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Excel VBA 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(V AL1, Range, OFFSET, TF)) < False Then VLOOKUP_NA = NAVALUE ElseIf WorksheetFunction.IsNA(WorksheetFunction.VLookup(V AL1, Range, OFFSET, TF)) = False Then VLOOKUP_NA = WorksheetFunction.VLookup(VAL1, Range, OFFSET, TF) End If End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Excel VBA Question
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 In article , Michael Saathoff wrote: 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(V AL1, Range, OFFSET, TF)) < False Then VLOOKUP_NA = NAVALUE ElseIf WorksheetFunction.IsNA(WorksheetFunction.VLookup(V AL1, Range, OFFSET, TF)) = False Then VLOOKUP_NA = WorksheetFunction.VLookup(VAL1, Range, OFFSET, TF) End If End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Excel VBA Question
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Excel VBA Question
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. "Michael Saathoff" wrote: 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(V AL1, Range, OFFSET, TF)) < False Then VLOOKUP_NA = NAVALUE ElseIf WorksheetFunction.IsNA(WorksheetFunction.VLookup(V AL1, Range, OFFSET, TF)) = False Then VLOOKUP_NA = WorksheetFunction.VLookup(VAL1, Range, OFFSET, TF) End If End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple Excel Question | Excel Discussion (Misc queries) | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
Simple Excel Question? | Excel Programming | |||
very simple VBA question for Excel | Excel Programming | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) |