Follow-up to WorkSheet Function
 

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

Follow-up to WorkSheet Function
 

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

--
Don Guillett
SalesAid Software

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

Follow-up to WorkSheet Function
 

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" wrote in message
...
1st.
You didn't use range(a1:b3)

--
Don Guillett
SalesAid Software

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

Follow-up to WorkSheet Function
 

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


--
Don Guillett
SalesAid Software

"Dthmtlgod" wrote in message
...
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" wrote in message
...
1st.
You didn't use range(a1:b3)

--
Don Guillett
SalesAid Software

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

Follow-up to WorkSheet Function
 

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 wrote:

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

--

Dave Peterson

Follow-up to WorkSheet Function
 

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.


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


--
Don Guillett
SalesAid Software

"Dthmtlgod" wrote in message
...
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" wrote in message
...
1st.
You didn't use range(a1:b3)

--
Don Guillett
SalesAid Software

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