Cannot get MATCH function in VBA to work

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,607
Office Version
  1. 365
  2. 2010
I'm using this:

Code:
Application.WorksheetFunction.Match(Str, Sheets("Master").Range("C12:C300"), 0)

in my routine.
Str is a text string (a person's name in the format "lastname, first name" -- such as Str = "Brown, John") and getting "unable to get match property..." error.
What's wrong with my statement?

The error happens when a person is not found. I wanted to capture the error to test to see if the person is found or not.
 
Last edited:

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,070
Office Version
  1. 2016
Platform
  1. Windows
This error occurs when no match is found. Is Str a correct string?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,607
Office Version
  1. 365
  2. 2010
Yes, Str is a valid string (person's name) but I wanted to know (capture) the fact that it is NOT found in the list of names if that's the case.
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,070
Office Version
  1. 2016
Platform
  1. Windows
Application.WorksheetFunction.Match gives an error when there is no match.
Application.Match does that not.

VBA Code:
MatchRow= Application.Match(Str, Sheets("Master").Range("C12:C300"), 0)
If IsError(MatchRow) Then
     MsgBox "No match found"
Else
    MsgBox "Match found in row " & MatchRow
End If
 
Solution

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,607
Office Version
  1. 365
  2. 2010
Thank you! Playing around, I just discovered that as you answered. Much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,064
Messages
5,628,388
Members
416,315
Latest member
certainlyfrustrated

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top