IsError & Match Function In VBA Code

Re: IsError & Match Function In VBA Code

If IsError(Application.Match(StartAT, GradeArray, 0)) Then

Re: IsError & Match Function In VBA Code

Bob,
Could you give some more details about your solution, what the diffrerence with the previous statement.
Thanks
PCI

Re: IsError & Match Function In VBA Code

The original code had Application and WorksheetFunction objcets, I stripped out WorksheetFunction.

You can use APplication or WorksheetFunction in most cases, but errors are handled differently. When a function is called directly using Application, such as Application.VLookup, the result is a variant containing an error. When called directly using WorksheetFunction, for example WorksheetFunction.VLookup, the function will raise an error. Both can be managed, but in different ways

Dim res As Variant
res = Application.VLookup(1, Range("A1:B10"), 2, False)

res = WorksheetFunction.VLookup(1, Range("A1:B10"), 2, False)

Here, the VLOOKUP function is being invoked to lookup a value that does not exist in the range A1:B10. The first method, Application.Vlookup, returns an error to the variable, whereas the second method raises an error.

Application calls can also be trapped using the IsError statement

If IsError(Application.VLookup(1, Range("A1:B10"), 2, False)) Then
        Debug.Print "error"
    End If

In a similar manner, WorksheetFunction calls can be wrapped in error handling code to trap the errors.

Re: IsError & Match Function In VBA Code

Thank you Bob, very clear.