| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro match function problem
Hi all,
I have a problem to create a match function macro with the following senario, whereby the lookup_array is currently at col D1 to D4, and the lookup_value will be A1. The problem is my lookup_array number will change to (D1 to D9) or (D1 to D15) or any row at row D based on new data inserted.. How to create a match macro if my lookup_array is changing everyday? A B C D 1 123 1 2 12 3 23 4 123 thank you.. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro match function problem
Why dont you refer the whole column. Refer responses for your other post...
-- Jacob (MVP - Excel) "Asraf" wrote: Hi all, I have a problem to create a match function macro with the following senario, whereby the lookup_array is currently at col D1 to D4, and the lookup_value will be A1. The problem is my lookup_array number will change to (D1 to D9) or (D1 to D15) or any row at row D based on new data inserted.. How to create a match macro if my lookup_array is changing everyday? A B C D 1 123 1 2 12 3 23 4 123 thank you.. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro match function problem
Hi
Application.Match(Range("A1"), Range("D:D"), 0) -- Regards Roger Govier Asraf wrote: Hi all, I have a problem to create a match function macro with the following senario, whereby the lookup_array is currently at col D1 to D4, and the lookup_value will be A1. The problem is my lookup_array number will change to (D1 to D9) or (D1 to D15) or any row at row D based on new data inserted.. How to create a match macro if my lookup_array is changing everyday? A B C D 1 123 1 2 12 3 23 4 123 thank you.. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro match function problem
Dear Jacob,
By refering to whole column will slow down my macro due to calculation on every single cell. by the way my actuall senario data is much heavy and not constant. i need a macro which will change my lookup_array respectively based on data in coll D "Jacob Skaria" wrote: Why dont you refer the whole column. Refer responses for your other post... -- Jacob (MVP - Excel) "Asraf" wrote: Hi all, I have a problem to create a match function macro with the following senario, whereby the lookup_array is currently at col D1 to D4, and the lookup_value will be A1. The problem is my lookup_array number will change to (D1 to D9) or (D1 to D15) or any row at row D based on new data inserted.. How to create a match macro if my lookup_array is changing everyday? A B C D 1 123 1 2 12 3 23 4 123 thank you.. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro match function problem
Asraf, please check the responses for your other post.
Dim lngRow As Long lngRow = Cells(Rows.Count, ActiveCell.Offset(, 2).Column).End(xlUp).Row ActiveCell.FormulaR1C1 = "=MATCH(RC[1],R1C[2]:R" & lngRow & "C[2],0)" -- Jacob (MVP - Excel) "Asraf" wrote: Dear Jacob, By refering to whole column will slow down my macro due to calculation on every single cell. by the way my actuall senario data is much heavy and not constant. i need a macro which will change my lookup_array respectively based on data in coll D "Jacob Skaria" wrote: Why dont you refer the whole column. Refer responses for your other post... -- Jacob (MVP - Excel) "Asraf" wrote: Hi all, I have a problem to create a match function macro with the following senario, whereby the lookup_array is currently at col D1 to D4, and the lookup_value will be A1. The problem is my lookup_array number will change to (D1 to D9) or (D1 to D15) or any row at row D based on new data inserted.. How to create a match macro if my lookup_array is changing everyday? A B C D 1 123 1 2 12 3 23 4 123 thank you.. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro match function problem
Dear Jacob,
thanks for your recent and fast reply. how if my lookup_array is in another sheets? let say my lookup array data is in "sheet2". "Jacob Skaria" wrote: Asraf, please check the responses for your other post. Dim lngRow As Long lngRow = Cells(Rows.Count, ActiveCell.Offset(, 2).Column).End(xlUp).Row ActiveCell.FormulaR1C1 = "=MATCH(RC[1],R1C[2]:R" & lngRow & "C[2],0)" -- Jacob (MVP - Excel) "Asraf" wrote: Dear Jacob, By refering to whole column will slow down my macro due to calculation on every single cell. by the way my actuall senario data is much heavy and not constant. i need a macro which will change my lookup_array respectively based on data in coll D "Jacob Skaria" wrote: Why dont you refer the whole column. Refer responses for your other post... -- Jacob (MVP - Excel) "Asraf" wrote: Hi all, I have a problem to create a match function macro with the following senario, whereby the lookup_array is currently at col D1 to D4, and the lookup_value will be A1. The problem is my lookup_array number will change to (D1 to D9) or (D1 to D15) or any row at row D based on new data inserted.. How to create a match macro if my lookup_array is changing everyday? A B C D 1 123 1 2 12 3 23 4 123 thank you.. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro match function problem
sorry Roger. by selecting all column D will slow down my macro.
"Roger Govier" wrote: Hi Application.Match(Range("A1"), Range("D:D"), 0) -- Regards Roger Govier Asraf wrote: Hi all, I have a problem to create a match function macro with the following senario, whereby the lookup_array is currently at col D1 to D4, and the lookup_value will be A1. The problem is my lookup_array number will change to (D1 to D9) or (D1 to D15) or any row at row D based on new data inserted.. How to create a match macro if my lookup_array is changing everyday? A B C D 1 123 1 2 12 3 23 4 123 thank you.. . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro match function problem
Suppose you have data in Sheet2 colD. The below will insert a formula in
active sheet cell B1 to match the content in cell A1 with Sheet2 ColD... Dim ws As Worksheet, rngData As Range Set ws = Sheets("Sheet2") Set rngData = ws.Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row) Range("B1").Formula = "=MATCH(A1," & rngData.Address & ",0)" -- Jacob (MVP - Excel) "Asraf" wrote: Dear Jacob, thanks for your recent and fast reply. how if my lookup_array is in another sheets? let say my lookup array data is in "sheet2". "Jacob Skaria" wrote: Asraf, please check the responses for your other post. Dim lngRow As Long lngRow = Cells(Rows.Count, ActiveCell.Offset(, 2).Column).End(xlUp).Row ActiveCell.FormulaR1C1 = "=MATCH(RC[1],R1C[2]:R" & lngRow & "C[2],0)" -- Jacob (MVP - Excel) "Asraf" wrote: Dear Jacob, By refering to whole column will slow down my macro due to calculation on every single cell. by the way my actuall senario data is much heavy and not constant. i need a macro which will change my lookup_array respectively based on data in coll D "Jacob Skaria" wrote: Why dont you refer the whole column. Refer responses for your other post... -- Jacob (MVP - Excel) "Asraf" wrote: Hi all, I have a problem to create a match function macro with the following senario, whereby the lookup_array is currently at col D1 to D4, and the lookup_value will be A1. The problem is my lookup_array number will change to (D1 to D9) or (D1 to D15) or any row at row D based on new data inserted.. How to create a match macro if my lookup_array is changing everyday? A B C D 1 123 1 2 12 3 23 4 123 thank you.. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro match function problem
Are you sure?
There are lots of functions in excel that limit themselves to the usedrange. Asraf wrote: sorry Roger. by selecting all column D will slow down my macro. "Roger Govier" wrote: Hi Application.Match(Range("A1"), Range("D:D"), 0) -- Regards Roger Govier Asraf wrote: Hi all, I have a problem to create a match function macro with the following senario, whereby the lookup_array is currently at col D1 to D4, and the lookup_value will be A1. The problem is my lookup_array number will change to (D1 to D9) or (D1 to D15) or any row at row D based on new data inserted.. How to create a match macro if my lookup_array is changing everyday? A B C D 1 123 1 2 12 3 23 4 123 thank you.. . -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro match function problem
Thanks Jacob. Glad to learn something new from you.. By the way is there any
websites that i can refer or learn VBA? i would like to learn more and master all this like you do. "Jacob Skaria" wrote: Suppose you have data in Sheet2 colD. The below will insert a formula in active sheet cell B1 to match the content in cell A1 with Sheet2 ColD... Dim ws As Worksheet, rngData As Range Set ws = Sheets("Sheet2") Set rngData = ws.Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row) Range("B1").Formula = "=MATCH(A1," & rngData.Address & ",0)" -- Jacob (MVP - Excel) "Asraf" wrote: Dear Jacob, thanks for your recent and fast reply. how if my lookup_array is in another sheets? let say my lookup array data is in "sheet2". "Jacob Skaria" wrote: Asraf, please check the responses for your other post. Dim lngRow As Long lngRow = Cells(Rows.Count, ActiveCell.Offset(, 2).Column).End(xlUp).Row ActiveCell.FormulaR1C1 = "=MATCH(RC[1],R1C[2]:R" & lngRow & "C[2],0)" -- Jacob (MVP - Excel) "Asraf" wrote: Dear Jacob, By refering to whole column will slow down my macro due to calculation on every single cell. by the way my actuall senario data is much heavy and not constant. i need a macro which will change my lookup_array respectively based on data in coll D "Jacob Skaria" wrote: Why dont you refer the whole column. Refer responses for your other post... -- Jacob (MVP - Excel) "Asraf" wrote: Hi all, I have a problem to create a match function macro with the following senario, whereby the lookup_array is currently at col D1 to D4, and the lookup_value will be A1. The problem is my lookup_array number will change to (D1 to D9) or (D1 to D15) or any row at row D based on new data inserted.. How to create a match macro if my lookup_array is changing everyday? A B C D 1 123 1 2 12 3 23 4 123 thank you.. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro match function problem
Yes i'm very sure. by calculating all cells in D will slow down the macro.
but if we can select several cell which is only "cell with data", it will increace the speed for calculation and the macro will not stuck in "calculating" progress for a long period. just assume if my lookup_array and lookup_value data is 30000 rows long.. how long it will take to match if we selecting all D as a lookup_array, compare with selecting just "cell with data". "Dave Peterson" wrote: Are you sure? There are lots of functions in excel that limit themselves to the usedrange. Asraf wrote: sorry Roger. by selecting all column D will slow down my macro. "Roger Govier" wrote: Hi Application.Match(Range("A1"), Range("D:D"), 0) -- Regards Roger Govier Asraf wrote: Hi all, I have a problem to create a match function macro with the following senario, whereby the lookup_array is currently at col D1 to D4, and the lookup_value will be A1. The problem is my lookup_array number will change to (D1 to D9) or (D1 to D15) or any row at row D based on new data inserted.. How to create a match macro if my lookup_array is changing everyday? A B C D 1 123 1 2 12 3 23 4 123 thank you.. . -- Dave Peterson . |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro match function problem
When you created a new worksheet and populated 10 rows with data, what was the
difference in times using the different ranges? Asraf wrote: Yes i'm very sure. by calculating all cells in D will slow down the macro. but if we can select several cell which is only "cell with data", it will increace the speed for calculation and the macro will not stuck in "calculating" progress for a long period. just assume if my lookup_array and lookup_value data is 30000 rows long.. how long it will take to match if we selecting all D as a lookup_array, compare with selecting just "cell with data". "Dave Peterson" wrote: Are you sure? There are lots of functions in excel that limit themselves to the usedrange. Asraf wrote: sorry Roger. by selecting all column D will slow down my macro. "Roger Govier" wrote: Hi Application.Match(Range("A1"), Range("D:D"), 0) -- Regards Roger Govier Asraf wrote: Hi all, I have a problem to create a match function macro with the following senario, whereby the lookup_array is currently at col D1 to D4, and the lookup_value will be A1. The problem is my lookup_array number will change to (D1 to D9) or (D1 to D15) or any row at row D based on new data inserted.. How to create a match macro if my lookup_array is changing everyday? A B C D 1 123 1 2 12 3 23 4 123 thank you.. . -- Dave Peterson . -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro match function problem
The difference is my data will be increase day by day and not a constant.
thats why i need a macro which can count row with data which will select the last row with data as my lookup_array. thanks to Jacob Skaria for the light he gave. he sure a excel monster. "Dave Peterson" wrote: When you created a new worksheet and populated 10 rows with data, what was the difference in times using the different ranges? Asraf wrote: Yes i'm very sure. by calculating all cells in D will slow down the macro. but if we can select several cell which is only "cell with data", it will increace the speed for calculation and the macro will not stuck in "calculating" progress for a long period. just assume if my lookup_array and lookup_value data is 30000 rows long.. how long it will take to match if we selecting all D as a lookup_array, compare with selecting just "cell with data". "Dave Peterson" wrote: Are you sure? There are lots of functions in excel that limit themselves to the usedrange. Asraf wrote: sorry Roger. by selecting all column D will slow down my macro. "Roger Govier" wrote: Hi Application.Match(Range("A1"), Range("D:D"), 0) -- Regards Roger Govier Asraf wrote: Hi all, I have a problem to create a match function macro with the following senario, whereby the lookup_array is currently at col D1 to D4, and the lookup_value will be A1. The problem is my lookup_array number will change to (D1 to D9) or (D1 to D15) or any row at row D based on new data inserted.. How to create a match macro if my lookup_array is changing everyday? A B C D 1 123 1 2 12 3 23 4 123 thank you.. . -- Dave Peterson . -- Dave Peterson . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem with MATCH () function . . . | Excel Worksheet Functions | |||
| Match function problem | Excel Worksheet Functions | |||
| Match function problem | Excel Worksheet Functions | |||
| MATCH function problem | Excel Discussion (Misc queries) | |||
| Problem with match function | Excel Discussion (Misc queries) | |||
Linear Mode
