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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Excel VBA Question
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 "JMB" wrote: chip has some discussion on his site http://www.cpearson.com/excel/Callin...onsFromVBA.htm but I've not yet found an explanation for exactly why that happens. it seems to behave like that for match, hlookup, lookup, and ceiling. I guess it is something to be cognizant of if the function can return an error (such as #NA or #NUM). "Dave" wrote: 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 "JMB" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Excel VBA Question
Some additional links that you may find have helpful information (and/or some
nifty downloads) http://www.decisionmodels.com/ http://erlandsendata.no/english/ http://www.contextures.com/ http://j-walk.com/ http://vb.mvps.org/ http://mcgimpsey.com/ http://xcell05.free.fr/ http://xldynamic.com/ http://mvps.org (look for the excel link on the right). "Dave" wrote: 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 "JMB" wrote: chip has some discussion on his site http://www.cpearson.com/excel/Callin...onsFromVBA.htm but I've not yet found an explanation for exactly why that happens. it seems to behave like that for match, hlookup, lookup, and ceiling. I guess it is something to be cognizant of if the function can return an error (such as #NA or #NUM). "Dave" wrote: 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 "JMB" wrote: 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) |