Prev: Copy and paste to the last Row of columb A
Next: Validation list in data entry form
From: Asraf on 5 May 2010 22:59
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
> .
>
From: Dave Peterson on 6 May 2010 08:06
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
From: Asraf on 7 May 2010 12:25
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
> .
>
First  |  Prev  | 
Pages: 1 2 3
Prev: Copy and paste to the last Row of columb A
Next: Validation list in data entry form