Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error using 'Match' WorksheetFunction in Macro

    Part of a macro I

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error using 'Match' WorksheetFunction in Macro

    Would the Find method give you what you need?
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Mar 2001
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error using 'Match' WorksheetFunction in Macro

    I cannot use the 'Find' WorksheetFunction because
    I get a Run-Time error '13' Type mismatch.

    Argument 2 in the function call is looking for a single search string whereas I am searching among a column of
    strings.
    ================================================== =====
    Proper usage of find:

    Dim status As Boolean
    refDes = "some generated text"
    status = CBool(Application.WorksheetFunction.
    _Find(refDes, Range("A26"), 1))

    My requirement creating mismatch error:

    Dim status As Boolean
    refDes = "some generated text"
    status = CBool(Application.WorksheetFunction.
    _Find(refDes, Range("A:A"), 1))

    ================================================== ========

    However, even when I use the 'Find' WorksheetFunction correctly, it yields a similar error. It returns TRUE
    when I find a string within another string as expected,
    but returns the error:

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error using 'Match' WorksheetFunction in Macro

    I was talking about the Find Method, not the worksheet function. If you are getting errors using the Find Method, show us the code and we will see if we can help with that.
    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error using 'Match' WorksheetFunction in Macro

    You need to work around this problem. Actually, in your spreadsheet, the match function gives '#VALUE' if the string searched is not found. In VBA this generates an error. You have to capture the error by doing some error trapping, e.g.

    <pre>Sub test()

    Dim status As Boolean
    Dim refDes As String

    refDes = "some generated string"
    On Error Resume Next
    status = CBool(Application.WorksheetFunction. _
    Match(refDes, Range("A:A"), 0))
    If Err.Number <> 0 Then
    MsgBox "String not found"
    Else
    Err.Clear
    MsgBox "String found"
    End If
    on error goto 0
    End Sub
    </pre>


    This code works around the problem: if an error is generated, the Err.Number will be different from zero and you can use this to proceed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •