Application.WorksheetFunction.Match in VBA code

TomCon

Active Member
Joined
Mar 31, 2011
Messages
272
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Is the Match function available within VBA as Application.WorksheetFunction.Match? When i am authoring code, it is in the dropdown when i type Application.WorksheetFunction, so i just use TAB to select it. However when the code executes, i get the error message "Unable to get the Match property of the WorksheetFunction class". I am trying to assign the result to an integer, as in

ckval=Application.WorksheetFunction.Match("text",r,0)

Thanks if you can clarify this for me!

Tom
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
Not enirely sure but it would appear match operates differently in VBA

WorksheetFunction.Match Method

Returns the relative position of an item in an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
If "text1" is not in column A:
Rich (BB code):
Application.WorksheetFunction.Match("text1", Range("A:A"), 0): Rem "Method Match of WorksheetFunction failed"

Application.Match("text1", Range("A:A"), 0): Rem  Type mismatch error (cannot MsgBox CVErr(xlErrNA))

Dim chkVal as Variant

chkVal = Application.WorksheetFunction.Match("text1", Range("A:A"), 0): Rem "Method Match of WorksheetFunction failed"

chkVal = Application.Match("text1", Range("A:A"), 0): Rem no error
This works for me. If "text" is not present, Application.Match d
 

TomCon

Active Member
Joined
Mar 31, 2011
Messages
272
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Thanks guys for the comments. What i found, after looking at your remarks and taking a look again is that my call to MATCH (in VBA) was resulting in an error, as i was using "exact" match, and there was none. I was mislead by the error message which made me think that the function was not supported, and i thought "how could that be if its in the dropdown in the editor". I guess the moral of the story is, "don't pay attention to the wording of the error message, and just look at the code".

Thanks!
Tom
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

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