Match function in VBA

[esbee]

I have Ms Office 2003.
I am unable to use Match function in VBA. I tried both
Application.worksheetfunction.match as well as Application.match.
I have data in 118 rows from column "A" to Column "L". I want to find the
row which matches three text criteria (D,K & L) and on numerical criteria
("A"). I was trying to capture the row number in cell "Q2" to check whether
the formula is working in VBA and to develop the programme thereafter. I used
"$" mark to array addresses but these were declared as invalid characters. I
used "" to enclose the addresses. That also did not work. I code reads as
under:
=======
Sub matching_rows()
Worksheets("sheet2").Activate
Range("q2").Value = Application.Match(1, _
(L1:L118 = "Sep") * (K1:K118 = "Cricket") * _
(D1118 = "Off") * (A1:A118 < 1), 0)
End Sub
=======

Can any one help ? Thanks in advance.

 

[JLGWhiz]

I have never used this function, so I am guessing, but give this a try.

Sub matching_rows()
Worksheets("sheet2").Activate
Range("q2").Value = Application.WorksheetFunction.Match(1, _
Array((L1:L118 = "Sep"), (K1:K118 = "Cricket"), _
(D1118 = "Off"), (A1:A118 < 1)), 0)
End Sub

 

[Equiangular]

Hi,

You may try this

Range("q2").FormulaArray = "=MATCH(1, (L1:L118 = ""Sep"")" & _
" * (K1:K118 = ""Cricket"") * (D1118 = ""Off"")" & _
" * (A1:A118 < 1), 0)"

 

[esbee]

Thank you for the response. But this is not working. It is not accepting
the colon marks ":" and declaring them as invalid characters.

 

[esbee]

Thank you. It is working. Though my intention to take the value to "Q2" cell
was only to check whether the formula is working correctly or not ( but not
to have any value at "Q2") , I have found a new use. I can now develop the
remaining code based on the value at "Q2". Thanks once again.

 

[esbee]

Is there a way to use a variable name, say, my_last_row in the formula where
ever the number "118" appears in the range addresses ?

Sorry for the trouble.

 

[Equiangular]

Welcome
You could try this.

Range("q2").FormulaArray = "=MATCH(1, (L1:L" & my_last_row & " =
""Sep"")" & _
" * (K1:K" & my_last_row & " = ""Cricket"")" & _
"* (D1" & my_last_row & " = ""Off"")" & _
" * (A1:A" & my_last_row &" < 1), 0)"

 

[esbee]

Many thanks. It's working fine. I didn't know that even '&my_last_row&'
should be enclosed in double quotes. Thanks for this knowledge.

 

[Equiangular]

You should not enclose a variable name in double quotes
& is for concatenating two strings

e.g.

Name="tom"
MsgBox "Hello! " & Name

The output is
Hello! Tom

When you need to display a quote, you need to use two quotes

e.g.

s="test "" quote"
MsgBox s

The output is
test " quote

 

[esbee]

Thanks once again. I realised my mistake after posting my reply. Now I am
struggling with the quotes while trying to replace the name "Cricket" by a
variable name "game_name" to generalise the code. It is playing truant with
me. Still I shall try for some more time and if I fail I shall once again
approach you. Thanks for sparing so much of your time for me.

 

[Equiangular]

Welcome

 

[esbee]

I am sorry I couldn't make any headway. I tried to replace "Cricket" by
variable name "game_name" and "Off " by the variable "Position". I get error
messages such as syntax error or expected:end of statement or unable to enter
formulaArray in range class etc., when I tried to tinker with the code' "&"
and "" signs. The code is like this. Can you help me ?

Range("q2").FormulaArray = "=MATCH(1, (L1:L" & my_last_row & " =
""Sep"")" & _
" * (K1:K" & my_last_row & = &game_name&) & _
"* (D1" & my_last_row & " = &postiion&)" & _
" * (A1:A" & my_last_row &" < 1), 0)"

 

[Equiangular]

I'm sorry for the late reply.

The corrected code is like this:

Range("q2").FormulaArray = "=MATCH(1, (L1:L" & my_last_row & " =
""Sep"")" & _
" * (K1:K" & my_last_row & " = """ & game_name & """)" & _
"* (D1" & my_last_row & " = """ & position & """)" & _
" * (A1:A" & my_last_row &" < 1), 0)"

You should add a space between the variable name and &.
To use quote in a string you need to use two consecutive quotes "".

 

[esbee]

Many thanks. I tried the three double quotes, it didn't work because of the
space between & and the variable name. Now it is working. Thanks once again
for your patient with me.

 

[esbee]

Many thanks. I tried the three double quotes,but it didn't work perhaps
because of not giving a space between "&" and the variable name. Now it is
working. Thanks once again for being patient with me.