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

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

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

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.

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.

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

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.

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.

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.