Follow-up to WorkSheet Function

[Dthmtlgod]

Here is my code and after reading the help I found that the error is being
caused by the value not being found, which I think would generate #N/A if
this one used as a formula.

How do I get around this? Here is the code so far with help from Frank.

With Application.WorksheetFunction
Res = .Index(Range("LOOKUP81!$A$1:$B$3"), .Match(Range("81!F" & Z),
Range("LOOKUP81!$A$1:$A$3"), 0), 2)
End With

 

[Don Guillett]

1st.
You didn't use range(a1:b3)

 

[Dthmtlgod]

The code works fine. When it can't find a match in the lookup, then I get
the run-time error.

This is where I am at. The first record it will find a match and it inserts
the value as expected. The second entry it doesn't find a match and it
errors out.

With Application.WorksheetFunction
Res = .Index(Range("LOOKUP81!$A$1:$B$3"), .Match(Range("81!F" & Z),
Range("LOOKUP81!$A$1:$A$3"), 0), 2)
End With
If Application.WorksheetFunction.IsNA(Res) Then
Range("81!f" & Z).Value = Range("81!F" & Z)
Else
Range("81!F" & Z).Value = Res
End If

 

[Don Guillett]

Sorry, you're correct. I don't think you can use isna in a macro. Try
on error goto xxx
instead

 

[Dave Peterson]

I find dropping the .worksheetfunction easier:

dim res as variant

with application
res = .Index(Range("LOOKUP81!$A$1:$B$3"), _
.Match(Range("81!F" & Z), Range("LOOKUP81!$A$1:$A$3"), 0), 2)
end with

if iserror(res) then
'it's an error
else
'keep going
end if


Did you drop Lookup from this portion: .Match(Range("81!F"

It looks different.

 

[Dthmtlgod]

You are correct. What I meant was the ISNA is the part that doesn't work.
All the other parts did.

Thanks for your assistance.