Prev: Copy and paste to the last Row of columb A
Next: Validation list in data entry form
From: Asraf on 5 May 2010 03:35
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 03:42
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: Roger Govier on 5 May 2010 03:49
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: Asraf on 5 May 2010 03:51
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: Jacob Skaria on 5 May 2010 03:54
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..
> > >
 |  Next  |  Last
Pages: 1 2 3
Prev: Copy and paste to the last Row of columb A
Next: Validation list in data entry form