How to Fix Error: Unable to get the Match property of the WorksheetFunction - VBA and VB.Net Tutorials, Education and Programming Services (2024)

Description

This error in matching occurs when the Match function is used, but a match was not found.

The Match function is used in order to determine the position in a 1-dimension list at which a text or number is matched.

This error is of type ‘Run-time 1004’. A run-time error is the type of error that occurs during the execution of the code. VBA does not know about it prior to actually running the code. It stops the execution of the code.

The particular error ‘1004’ is related to an application-defined or object-defined error.

In other words, this particular variation of Run-time error occurs when VBA cannot identify an object. The Match function is supposed to return a match object. When a match is not found, VBA detects that there’s an unidentified match object, and that’s why it throws this particular Run-time 1004 error.

Take a look at https://software-solutions-online.com/run-time-error-1004-in-vba/ for other examples of run time errors.

Example 1: Use Application.Match Instead

A notable issue with using WorksheetFunction.Match is that it throws an error when the match is not found, which complicates things slightly (we will see in the next example how to deal with that). But why not use Application.Match instead?

It is the same exact thing, but without the error throwing part. This means that it will work exactly like WorksheetFunction.Match when a match is found, but when a match is not found, it will not throw an error.

Furthermore, Application.Match will recognize that there is an error and will be able to point that out for you if you ask it to. Another way to look at this is to think of WorksheetFunction.Match as a function that attempts to create a match object, and if it does not find a match, it does not know what to do.

But Application.Match is a function that attempts to create a match object and if it does not find a match, it instead creates an Error Object (that is why there is no error thrown).

Error throwing is disruptive; it halts the progress of the code and you end up with no valid objects to work with. But when you have an Error Object, then you have a valid object that you can work with. In this example, we will attempt to match a value in column 1 that does not exist. We will keep column 1 empty initially.

Sub No_Match()matchRow = Application.WorksheetFunction.Match("test", Columns(1), 0)End Sub

To avoid the error we will replace WorksheetFunction.Match with Application.Match:

Sub No_Match()matchRow = Application.Match("test", Columns(1), 0)End Sub

The code will run without any issues. This should be sufficient to ensure that you never get the dreaded error that stops your entire application. In the next example, we will see how we can handle the error in a more meaningful way that fits our purpose.

Example 2: Handling the error

In this example, we will handle the error such that the flow of our code changes based on whether we got an error while matching or not.

This is usually very much needed in any real-world coding of applications because if we did not find what we’re looking for, we need to handle those issues.

Usually, we try to match a word in order to determine which row/column it is located at and hence do something with that row/column.

Sub mismatchErrorHandling()matchRow = Application.Match("test", Columns(1), 0)If IsError(matchRow) Then MsgBox "No Match"Else MsgBox "Match row is: " & matchRowEnd IfEnd Sub

Note that because we’re using Application.Match, we now can check if the matchRow object is an error object. If it is, then the statement IsError(matchRow) will evaluate to true and a message box will display “No Match.”

Summary

Utilizing Application.Match instead of WorksheetFunction.Match will ensure that the object that will be assigned the result of the matching is defined even if the match was not found.

If the match is found, then it will be assigned the integer value of the match. But if it’s not found, then the object will be assign an error value and will become an error object that contains the standard error descriptors, such as error name, description, number, etc. It will be easy to determine whether it is an error object or not through the use of IsError(checkObj).