Folks

I believe I am in need of an array to speed up my search. If on sheet1 I
have 26 columns of data but in columns 8 and 10 when certain criteria are
met I put these values could be location and route(route is a number) into 2
variables named respectively as mentioned. I then goto sheet2 where I have a
named range of about 1000 entries that are 4 columns wide, the first 2
columns being the location and route. My code at the moment starts at cell
A1 on sheet 2 and loops through the activecell until location and route are
either not blank or hit a blank - so basically it goes through the whole
list until it gets a match, and obviously each location can have more than 1
route. When it does it then grabs the data from column 3 and 4 which might
be "km" and "abb" variable names respectively and then drops them back onto
sheet1 say into columns 1 & 2 on the row that I was working on. I believe
what I need is an index / array to so that when the variables from sheet1
are found they goto the first cell address of the location name straight
away and look from there instead of always starting at the top and working
down. In sheet1 I may have 40,000 rows of data and out of these maybe a
thousand or more will meet the criteria and have to be searched. Can anyone
help to speed this up !!!!

Thanks in advance

Regards

Gav !!

Gav,

while putting the data in an array may speed things up (a
very little) there is another approach.

Consider using an index "lookup" formula for the data in
sheet2. Take the data from columns 3 & 4 from sheet2, add
(CONCATENATE) the data in a new column and then post
(programatically place) the data (Sheet1, Cols 8 & 10) you
need to lookup in the reference cell for the index
function.

The result value will be the "row" in sheet2 where your
searched for data exists. It should be relatively easy to
copy the cells from sheet2 (cols 1 & 2) that you need in
sheet1.



-----Original Message-----
Folks

I believe I am in need of an array to speed up my search.
If on sheet1 I
have 26 columns of data but in columns 8 and 10 when
certain criteria are
met I put these values could be location and route(route
is a number) into 2
variables named respectively as mentioned. I then goto
sheet2 where I have a
named range of about 1000 entries that are 4 columns
wide, the first 2
columns being the location and route. My code at the
moment starts at cell
A1 on sheet 2 and loops through the activecell until
location and route are
either not blank or hit a blank - so basically it goes
through the whole
list until it gets a match, and obviously each location
can have more than 1
route. When it does it then grabs the data from column 3
and 4 which might
be "km" and "abb" variable names respectively and then
drops them back onto
sheet1 say into columns 1 & 2 on the row that I was
working on. I believe
what I need is an index / array to so that when the
variables from sheet1
are found they goto the first cell address of the
location name straight
away and look from there instead of always starting at
the top and working
down. In sheet1 I may have 40,000 rows of data and out of
these maybe a
thousand or more will meet the criteria and have to be
searched. Can anyone
help to speed this up !!!!

Thanks in advance

Regards

Gav !!


.

look up Match in Excel help

Marty


-----Original Message-----
Folks

I believe I am in need of an array to speed up my search.
If on sheet1 I
have 26 columns of data but in columns 8 and 10 when
certain criteria are
met I put these values could be location and route(route
is a number) into 2
variables named respectively as mentioned. I then goto
sheet2 where I have a
named range of about 1000 entries that are 4 columns
wide, the first 2
columns being the location and route. My code at the
moment starts at cell
A1 on sheet 2 and loops through the activecell until
location and route are
either not blank or hit a blank - so basically it goes
through the whole
list until it gets a match, and obviously each location
can have more than 1
route. When it does it then grabs the data from column 3
and 4 which might
be "km" and "abb" variable names respectively and then
drops them back onto
sheet1 say into columns 1 & 2 on the row that I was
working on. I believe
what I need is an index / array to so that when the
variables from sheet1
are found they goto the first cell address of the
location name straight
away and look from there instead of always starting at
the top and working
down. In sheet1 I may have 40,000 rows of data and out of
these maybe a
thousand or more will meet the criteria and have to be
searched. Can anyone
help to speed this up !!!!

Thanks in advance

Regards

Gav !!


.