Efficient Search / Find
 

Hi all,

I am writing some code that needs to determine the row number of a cell
where the cell contents match.

I have two sorted named Ranges : currentWeek and previousWeek
The first column of each range is a persons name. I need to find the row
number in previousWeek where the persons name from currentWeek matches.
In some cases a person that exists in currentWeek may not exist in
previousWeek (in which case returning -1 would be fine)

I two loops to do this search, but in a list of upto 1500 names, the time it
takes is very long.

any suggestions would be appreciated.
thanks
Dave

Efficient Search / Find
 

Hi Dave,
try useing MATCH, something like this

dim varRow as variant
dim oCurrent as range
dim oPrevious as range

set oPrevious=Range(PreviousWeek).columns(1)

for each oCurrent in currentWeek.columns(1)
varRow=application.match(oCurrent,oPrevious,1)
if iserror(varRow) then varRow=-1
' do something
next oCurrent

This code does not exploit the fact that currentweek is also sorted, so
further optimisation may be possible.

hth
Charles Williams
www.DecisionModels.com


"MSNEWS" wrote in message
...
Hi all,

I am writing some code that needs to determine the row number of a cell
where the cell contents match.

I have two sorted named Ranges : currentWeek and previousWeek
The first column of each range is a persons name. I need to find the row
number in previousWeek where the persons name from currentWeek matches.
In some cases a person that exists in currentWeek may not exist in
previousWeek (in which case returning -1 would be fine)

I two loops to do this search, but in a list of upto 1500 names, the time
it
takes is very long.

any suggestions would be appreciated.
thanks
Dave