Troubleshooting Guide: How to Fix Unable to Get the Match Property of the WorksheetFunction Class in Excel

This guide will help you troubleshoot and fix the "Unable to Get the Match Property of the WorksheetFunction Class" error in Excel. This error typically occurs when using the WorksheetFunction.Match method in Excel VBA (Visual Basic for Applications). We will discuss the possible causes of this error and provide step-by-step solutions to resolve the issue.

Table of Contents

  1. Understanding the Match Function in Excel VBA
  2. Possible Causes of the Error
  3. Solutions to Fix the Error
  4. FAQs
  5. Related Links

Understanding the Match Function in Excel VBA

The WorksheetFunction.Match method is used in Excel VBA to find the position of a value within a specified range of cells. It returns the relative position of an item in a range that matches a specified value.

Here is a basic example of using the Match function in Excel VBA:

Sub FindMatchPosition()
    Dim ws As Worksheet
    Dim rng As Range
    Dim searchValue As Variant
    Dim position As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set rng = ws.Range("A1:A10")
    searchValue = "John"

    position = Application.WorksheetFunction.Match(searchValue, rng, 0)
    MsgBox "The position of the searched value is: " & position
End Sub

In this example, we are trying to find the position of the "John" in the range A1:A10 on Sheet1.

Possible Causes of the Error

The "Unable to Get the Match Property of the WorksheetFunction Class" error typically occurs when the specified value is not found in the given range. When the Match function cannot find the value, it throws an error instead of returning a result.

Solutions to Fix the Error

Solution 1: Use Error Handling

Add error handling to your VBA code to prevent the error message from being displayed when the value is not found. Here's an example of how to do this:

Sub FindMatchPosition()
    Dim ws As Worksheet
    Dim rng As Range
    Dim searchValue As Variant
    Dim position As Variant

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set rng = ws.Range("A1:A10")
    searchValue = "John"

    On Error Resume Next
    position = Application.WorksheetFunction.Match(searchValue, rng, 0)
    On Error GoTo 0
    
    If IsError(position) Then
        MsgBox "Value not found"
    Else
        MsgBox "The position of the searched value is: " & position
    End If
End Sub

In this example, we added the On Error Resume Next statement before the Match function, and then we check if the returned value is an error using IsError(position).

Solution 2: Use Application.Match Instead of WorksheetFunction.Match

Replace WorksheetFunction.Match with Application.Match to return an error value instead of throwing an error. Here's an example:

Sub FindMatchPosition()
    Dim ws As Worksheet
    Dim rng As Range
    Dim searchValue As Variant
    Dim position As Variant

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set rng = ws.Range("A1:A10")
    searchValue = "John"

    position = Application.Match(searchValue, rng, 0)
    
    If IsError(position) Then
        MsgBox "Value not found"
    Else
        MsgBox "The position of the searched value is: " & position
    End If
End Sub

In this example, we replaced Application.WorksheetFunction.Match with Application.Match, which returns an error value that can be checked using IsError(position).

FAQs

Q1: What is the difference between Application.Match and WorksheetFunction.Match?

A: Application.Match and WorksheetFunction.Match are similar in functionality, but they handle errors differently. Application.Match returns an error value when the value is not found, which can be checked using IsError(position). On the other hand, WorksheetFunction.Match throws an error when the value is not found, which requires error handling to be added to the code to prevent the error message from being displayed.

Q2: Can I use the Match function with a two-dimensional range?

A: No, the Match function is designed to work with one-dimensional ranges only. To search for a value in a two-dimensional range, you can use nested loops or combine Match with other functions like Index.

Q3: How can I use the Match function with case-sensitive criteria?

A: The Match function is not case-sensitive by default. To perform a case-sensitive search, you can use an array formula along with the EXACT function. Alternatively, you can loop through the range in VBA and use the StrComp function to compare strings with case-sensitive criteria.

Q4: Can I use the Match function to search for partial matches?

A: The Match function does not support partial matches directly. However, you can use a combination of Excel functions, such as SEARCH and IF, to create an array formula that searches for partial matches. In VBA, you can loop through the range and use the InStr function to search for partial matches.

Q5: How can I use the Match function to return multiple matches?

A: The Match function is designed to return the first match found in a range. To return multiple matches, you can use an array formula with the IF and ROW functions in Excel. In VBA, you can loop through the range and store the positions of multiple matches in an array or a collection.

Great! You’ve successfully signed up.

Welcome back! You've successfully signed in.

You've successfully subscribed to Lxadm.com.

Success! Check your email for magic link to sign-in.

Success! Your billing info has been updated.

Your billing was not updated.