Using MATCH function in VBA

[Guest]

I'm getting an "Unable to get the Match property of the WorksheetFunction
class" error message when the "i = WorksheetFunction.Match(..." line of code
is executed in the following code block:

Dim i As Integer
Dim SysRCNameTemp As String
Dim SysRCNameArray(25) As String

i = Application.WorksheetFunction.Match(SysRCNameTemp, _
SysRCNameArray(), 0)

Can someone tell me how to fix the aforementioned line of code? Thanks in
advance for any help.

 

[Guest]

I get that error with your code when the array does not contain the value you
are trying to match. This seems to work fine, though:

i = Application.Match(SysRCNameTemp, SysRCNameArray, 0)

With i dimmed as int, you will still get a type mismatch error if match
returns an error. You might consider dimming i as a variant (you could use
isnumeric to test it and see if match returned an error or a number).

 

[Guest]

Thanks for the suggestion! Dimensioning "i" as a Variant and using the
IsNumeric function to trap for a number or error did the trick!

 

[TomThumb]

Thank you both for your short but accurate exchange. You helped me too.

 

[esbee]

If I have a Match function like
Match(1,(range_name1=variable_name1)*(range_name2=variable_name2),0)
will you please tell me how I should rewrite the code given by you ?
range_name1 has text, range_name2 has numbers.