Efficient Search / Find

[MSNEWS]

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

 

[Charles Williams]

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