Macro match function problem
in [General]
|
From: Asraf on 5 May 2010 06:36 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.. > > > >
From: Asraf on 5 May 2010 06:40 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.. > > > . >
From: Jacob Skaria on 5 May 2010 07:30 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.. > > > > >
From: Dave Peterson on 5 May 2010 08:28 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
From: Asraf on 5 May 2010 21:41 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.. > > > > > >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Copy and paste to the last Row of columb A Next: Validation list in data entry form |