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(Ra nge("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

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

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)

"nyn04 " wrote in message
...
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/

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"),Rang e("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

"nyn04 " wrote in message
...
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/

for Vlookup as shown it should be:

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

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
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)

"nyn04 " wrote in message
...
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/