Application.WorksheetFunction.Match in VBA code

[TomCon]

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

 

[Dryver14]

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]

If "text1" is not in column A:

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]

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