Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)" |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)" |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find the second match using the match function | Excel Worksheet Functions | |||
how can we get unique values in match function for same match key. | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Match as well as does not match array function | Excel Discussion (Misc queries) | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions |