Using MATCH function in VBA
 

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.

Using MATCH function in VBA
 

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


"Bob" wrote:

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.

Using MATCH function in VBA
 

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


"JMB" wrote:

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


"Bob" wrote:

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.

Using MATCH function in VBA
 

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


"Bob" wrote:

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


"JMB" wrote:

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


"Bob" wrote:

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.

Using MATCH function in VBA
 

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.


--
esbee


"JMB" wrote:

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


"Bob" wrote:

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.