Using MATCH function in VBA

B

Bob

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.
 
J

JMB

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).
 
B

Bob

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top