look up and ISNA in vba

Discussion:

(too old to reply)

nyn04 >

2004-08-04 11:32:48 UTC

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

--
Message posted from http://www.ExcelForum.com

nyn04 >

2004-08-04 11:34:07 UTC

Permalink

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

--
Message posted from http://www.ExcelForum.com

Bob Phillips

2004-08-04 12:21:33 UTC

Permalink

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)

Post by 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
thanks
---
Message posted from http://www.ExcelForum.com/

Tom Ogilvy

2004-08-04 12:36:17 UTC

Permalink

for Vlookup as shown it should be:

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

Post by 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)

Tom Ogilvy

2004-08-04 12:34:16 UTC

Permalink

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.
--
Regards,
Tom Ogilvy