look up and ISNA in vba

N

nyn04

I am trying to write a vba code that accomplished the following exce
code,

can anyone help

=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

I have wrriten the following

If WorksheetFunction.IsNA(WorksheetFunction.Lookup(Range("a3")
Range("j1:j5"), Range("l1:l5"))) = True Then ....

but when I run I get an error messege telling that I can not find th
lookup function, I think its due to the fact that the return is N/
since when I put on a cell that has an answear I get a response
 
N

nyn04

IF(ISNA(LOOKUP(A3,t!A:A,t!C:C))<>TRUE,(LOOKUP(A3,t!A:A,t!C:C)),0)

this is the complete excel function that I am trying to code

thank
 
B

Bob Phillips

As before

Dim ans
On Error Resume Next
ans = WorksheetFunction.VLookup(Range("A3"),
worksheets("t").Range("A:A"), worksheets("t").Range("C:C"), 2, False)
On Error GoTo 0
If ans = "" Then
MsgBox "Empty"
Else
MsgBox ans
End If

not tested so you may need to teweak.

BTW LOOKUP and VLOOKUP are different.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Tom Ogilvy

Using WorksheetFunction as a qualifier results in a trappable error rather
than return N/A when the value is not found. If you just use application
as the qualifier, then N/A is returned.

Dim res as Variant

res = Application.Lookup(Range("A3"),Range("t!A:A"),Range("t!C:C"))
if iserror(res) then
msgbox Range("A3").Value & " not found"
res = 0
else
msgbox "Found, return is " & res
End if

'at this point, res holds zero or the value returned.


So you don't need to do two lookups in VBA.
 
T

Tom Ogilvy

for Vlookup as shown it should be:

WorksheetFunction.VLookup(Range("A3"), _
worksheets("t").Range("A:C"), 3, False)

--
Regards,
Tom Ogilvy

Bob Phillips said:
As before

Dim ans
On Error Resume Next
ans = WorksheetFunction.VLookup(Range("A3"),
worksheets("t").Range("A:A"), worksheets("t").Range("C:C"), 2, False)
On Error GoTo 0
If ans = "" Then
MsgBox "Empty"
Else
MsgBox ans
End If

not tested so you may need to teweak.

BTW LOOKUP and VLOOKUP are different.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Top