Match function in VBA
 

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.

--
esbee

Match function in VBA
 

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

"esbee" wrote:

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.

--
esbee

Match function in VBA
 

Hi,

You may try this

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

esbee wrote:
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.

Match function in VBA
 

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


"JLGWhiz" wrote:

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

"esbee" wrote:

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.

--
esbee

Match function in VBA
 

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

Match function in VBA
 

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.

--
esbee


"Equiangular" wrote:

Hi,

You may try this

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

esbee wrote:
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.

Match function in VBA
 

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

esbee wrote:
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.

Match function in VBA
 

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


"Equiangular" wrote:

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

esbee wrote:
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.

Match function in VBA
 

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 wrote:
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.

Match function in VBA
 

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


"Equiangular" wrote:

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 wrote:
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.

Match function in VBA
 

Welcome :)

esbee wrote:
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.

Match function in VBA
 

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

--
esbee


"Equiangular" wrote:

Welcome :)

esbee wrote:
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.

Match function in VBA
 

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

esbee wrote:
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)"

Match function in VBA
 

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


"Equiangular" wrote:

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

esbee wrote:
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)"

Match function in VBA
 

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.

--
esbee


"Equiangular" wrote:

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

esbee wrote:
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)"