Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Catching error on VBA Match function

  1. #1
    New Member
    Join Date
    Feb 2012
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Catching error on VBA Match function

    Hi,

    I'm using function Match in VBA (for a fuzzy text search) in the line:

    Code:
    Application.WorksheetFunction.Match("*" & Trim(Terms(i)) & "*", ActiveSheet.Range("B1:B250"), 0)
    Terms(i) is just a string, the function works fine except that eventually it doesn't find any matches. How could I catch this error using IF?

    I've tried this:

    Code:
    If IsError(Application.WorksheetFunction.Match("*" & Trim(Terms(i)) & "*", ActiveSheet.Range("B1:B250"), 0)) Then
                'do something
    End If
    but again, it throws an execution Debug error. Same for IsNull. IsNA is not available in VBA. Any ideas?

    Thanks, a.

  2. #2
    Board Regular gaj104's Avatar
    Join Date
    Nov 2002
    Location
    London, UK
    Posts
    864
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Catching error on VBA Match function

    Not the biggest fan of using excel workbook specific functions in VBA. Usually there is no reason to use them. So, in your example I would use Find, i.e.

    Code:
    If Not Range("B1:B250").Find("*" & Trim(Terms(i)) & "*") Is Nothing Then
        'match found
    Else
        'no match found
    End If

  3. #3
    New Member
    Join Date
    Feb 2012
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Catching error on VBA Match function

    I appreciate your answer.

    The reason I use Match is that it returns a position in a list (not a text). Therefore you can run a search multiple times with different search terms and then find the most likely cell using function Mode.

    No idea on how that would be done with Find, though.

    But there must be a way to catch errors with worksheet functions..... ??

  4. #4
    Board Regular
    Join Date
    Nov 2011
    Posts
    1,363
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Catching error on VBA Match function

    If it were "feet to the fire" for WorksheetFunction:

    Code:
    Sub x()
        terms = Array("A", "B", "A", "d")
        For i = 0 To UBound(terms)
            On Local Error Resume Next
            m = Application.WorksheetFunction.Match("*" & Trim(terms(i)) & "*", _
                                        ActiveSheet.Range("B1:B250"), 0)
            If Err <> 0 Then
                Debug.Print "not found: " & terms(i)
                Err.Clear
            Else
                Debug.Print "found: " & terms(i)
            End If
        Next i
    End Sub

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,626
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Catching error on VBA Match function

    Try this.
    Code:
    Dim res As Variant
     
    res = Application.Match("*" & Trim(Terms(i)) & "*", ActiveSheet.Range("B1:B250"), 0)
     
    If IsError(res) Then
         MsgBox "No match found"
    Else
         MgsBox "Match found in row " & res
    End If
    If posting code please use code tags.

  6. #6
    New Member
    Join Date
    Feb 2012
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Catching error on VBA Match function

    Thanks all, I've tried several solutions but Norie's seems to be the one.

    Oddly enough, the ONLY difference between her code and mine is/was:
    NORIE's (working)
    X = Application.Match("*" & Trim(Terms(i)) & "*", ActiveSheet.Range("B1:B250"))

    MINE (not working)
    X = Application.WorkSheetFunction.Match("*" & Trim(Terms(i)) & "*", ActiveSheet.Range("B1:B250"))

    Am I missing something? Aren't we supposed to include the WorkSheetFunction when using Match in VBA (or any other function)??

  7. #7
    Board Regular taurean's Avatar
    Join Date
    Jun 2011
    Location
    Muscat, Oman
    Posts
    2,173
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Catching error on VBA Match function

    Quote Originally Posted by a_ud View Post
    Hi,

    I'm using function Match in VBA (for a fuzzy text search) in the line:

    Code:
    Application.WorksheetFunction.Match("*" & Trim(Terms(i)) & "*", ActiveSheet.Range("B1:B250"), 0)
    Terms(i) is just a string, the function works fine except that eventually it doesn't find any matches. How could I catch this error using IF?

    I've tried this:

    Code:
    If IsError(Application.WorksheetFunction.Match("*" & Trim(Terms(i)) & "*", ActiveSheet.Range("B1:B250"), 0)) Then
                'do something
    End If
    but again, it throws an execution Debug error. Same for IsNull. IsNA is not available in VBA. Any ideas?

    Thanks, a.
    I believe you can use:
    Code:
    If IsNumeric(Application.Match("*" & Trim(Terms(i)) & "*", ActiveSheet.Range("B1:B250"), 0)) Then
    Debug.Print "Match found!"
    End If
    Regards,
    Shrivallabha Redij

    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    --------------------------------------------------------------------------------------------------------------
    "When you come to the end of your rope, tie a knot and hang on." - Franklin D. Roosevelt
    --------------------------------------------------------------------------------------------------------------

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,930
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Catching error on VBA Match function

    For the differences between Application.function_name and application.worksheetfunction.function_name see the Error Handling section on Chip's page here.

  9. #9
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,626
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Catching error on VBA Match function

    a ud

    You can use workhseet functions in VBA without using WorksheetFunction.

    The obvious advantage is that not using WorksheetFunction stops the code error.

    I can't remember exactly why/how it works though.
    If posting code please use code tags.

  10. #10
    Board Regular gaj104's Avatar
    Join Date
    Nov 2002
    Location
    London, UK
    Posts
    864
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Catching error on VBA Match function

    The reason I use Match is that it returns a position in a list (not a text). Therefore you can run a search multiple times with different search terms and then find the most likely cell using function Mode.
    A find returns a range. From there you can get any property of that range, i.e. the row (as what match does).

    i.e.

    Code:
    If Not Range("B1:B250").Find("*" & Trim(Terms(i)) & "*") Is Nothing Then
        'match found
        Set r = Range("B1:B250").Find("*" & Trim(Terms(i)) & "*")
        Debug.Print r.Row 'etc
    Else
        'no match found
    End If

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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