Match function in VBA

  • Thread starter Thread starter esbee
  • Start date Start date
E

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") * _
(D1:D118 = "Off") * (A1:A118 < 1), 0)
End Sub
=======

Can any one help ? Thanks in advance.
 
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"), _
(D1:D118 = "Off"), (A1:A118 < 1)), 0)
End Sub
 
Hi,

You may try this

Range("q2").FormulaArray = "=MATCH(1, (L1:L118 = ""Sep"")" & _
" * (K1:K118 = ""Cricket"") * (D1:D118 = ""Off"")" & _
" * (A1:A118 < 1), 0)"
 
Thank you for the response. But this is not working. It is not accepting
the colon marks ":" and declaring them as invalid characters.
 
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.
 
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.
 
Welcome :)
You could try this.

Range("q2").FormulaArray = "=MATCH(1, (L1:L" & my_last_row & " =
""Sep"")" & _
" * (K1:K" & my_last_row & " = ""Cricket"")" & _
"* (D1:D" & my_last_row & " = ""Off"")" & _
" * (A1:A" & my_last_row &" < 1), 0)"
 
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.
 
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
 
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.
 
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:D" & my_last_row & " = &postiion&)" & _
" * (A1:A" & my_last_row &" < 1), 0)"
 
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:D" & 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 "".
 
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.
 
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.
 
Back
Top