IsError & Match Function In VBA Code

  • Hi

    I am trying and failing to use this

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

    StartAT is a text string that might be blank or a space or other invalid entry, GradeArray is a table which I am using to get a numeric value from a valid text entry. If the entry is invalid then I will use a value from an adjacent cell.

    The problem is that I get the 1004 error.

    Can anybody shed some light on this?



  • Re: IsError & Match Function In VBA Code

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

    Triumph without peril brings no glory: Just try

  • 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.



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!