Announcement

Collapse
No announcement yet.

IsError & Match Function In VBA Code

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • IsError & Match Function In VBA Code



    Hi

    I am trying and failing to use this

    Code:
    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?

    Thanks

    Robert

  • #2
    Re: IsError & Match Function In VBA Code

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

    Bob

    Comment


    • #3
      Re: IsError & Match Function In VBA Code

      Hi Bob

      thanks, that seems to do the trick.

      Regards

      Robert

      Comment


      • #4
        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
        Triumph without peril brings no glory: Just try

        Comment


        • #5
          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

          Code:
              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.
          HTH

          Bob

          Comment


          • #6


            Re: IsError & Match Function In VBA Code

            Thank you Bob, very clear.
            Triumph without peril brings no glory: Just try

            Comment

            Working...
            X