Use VLOOKUP in code

[agibsonsw]

Hello. Excel 2003.

I'm using Application.WorksheetFunction.Vlookup in my code. But how can I catch the #N/A error?
I can do so with error handling code but this is not very elegant.

Thanks, Andy.

[HansV]

Use Application.VLookup instead of Application.WorksheetFunction.VLookup. If there is no match, Application.VLookup doesn't raise an error, but it returns an error value instead. You must declare the receiving variable as a Variant:

For example:

Code: Select all

Dim varLookup As Variant
varLookUp = Application.VLookup(3, Range("A1:B200"), 2, False)
If IsError(varLookup) Then
  ' code to run if VLookup returns an error
  ...
Else
  ' do something with varLookup
  ...
End If

[agibsonsw]

Thank you.