Take a look at Tom's suggestion once more.

dim ans4 as Variant

ans4 = Application.VLookup(Range("C2"),Range("B6:D2000"), 3, False)

If iserror(ans4) Then
ans4 = "Value Not Defined"
End If

ans4 does not retain the previous value in this example if there is no match.
ans4 returns an error. Then Tom checks for that error--hr doesn't check for the
string "Failed", either.

No "on error resume next" required.



Dan wrote:

The problem is that the error is VLookup and not ans4. ans4 still retained
the last value that was calculated with VLookup and therefore iserror (ans4)
will never be true.

To get around this:

dim ans4 as Variant

Do While Not EOF(FileNum)
ans4 = Application.VLookup(Range("C2"),Range("B6:D2000"), 3, False)
On Error Resume Next

If ans4 = "Failed" Then
ans4 = "Value Not Defined"
End If

.Offset(1, 15).Value = ans4
ans4 = "Failed"

Loop

Is there an alternate to this patch/bandaide?

Thanks

"Dan" wrote:

Not working.

If I leave the equation as below then the errow window appears and the app
quites. If I add "On Error Resume Next" after the VLookup command, the app
continues but the statement ans4 = "Value Not Defined" is skipped.

What am I missing?

"Tom Ogilvy" wrote:

The easy way is this approach:


dim ans4 as Variant

ans4 = Application.VLookup(Range("C2"),Range("B6:D2000"), 3, False)

If iserror(ans4) Then
ans4 = "Value Not Defined"
End If

..Offset(1, 15).Value = ans4

--
Regards,
Tom Ogilvy



"Dan" wrote in message
...
I am using an VLookup and want to execute a specific action if the value is
not returned.

ans4 =
Application.WorksheetFunction.VLookup(Range("C2"), Range("B6:D2000"),
3, False)
On Error Resume Next

If "AN ERROR HAS OCCURRED" Then
ans4 = "Value Not Defined"
End If

.Offset(1, 15).Value = ans4

How do I do this?

Thanks




--

Dave Peterson