On Error Resume Next

[Guest]

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

 

[Tom Ogilvy]

The easy way is this approach:


dim ans4 as Variant

ans4 = Application.VLookup(Range("C2"),Range("B62000"),3, False)

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

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

 

[Guest]

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?

 

[Guest]

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

 

[Dave Peterson]

Take a look at Tom's suggestion once more.

dim ans4 as Variant

ans4 = Application.VLookup(Range("C2"),Range("B62000"),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.

 

[Tom Ogilvy]

Vlookup (as I have implemented it) doesn't raise an error affected by On
Error Resume Next. The result of Vlookup is returned to ans4 each time.
If Vlookup is unsuccessful, ans4 will contain the #N/A error same as in the
worksheet. Otherwise it will contain the result.

Even if an error were raised (and let me say again, it isn't), putting On
Error Resume Next after the error has occurred does nothing.

Regards,
Tom Ogilvy