From: Asraf on
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
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
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
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
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..
> > >