MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Searching from sheet; cell=variable vs. find(variable)?


Posted by Pasi Vartiainen on November 10, 2000 4:37 AM

Which is faster method, use

for i = 1 to <some number>
if cell(1,1).value= variable then
next

or use

a=Cells.Find(What:=variable, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=
_xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
_.Activate

Are these methods equeal with speed, or are there some limit where other is faster than other? I mainly ask this because I have texbox which is updated every time some cell is clicked. I use for loop, there are some 300 lines. In my computed speed is not a problem, but I am afraid how slow my code is in computers which are equiped only with 64meg's RAM.

I use cell value from certain row as a key, which I use as a searched variable. If match is found I execute some more code, but thats other story.

So, I am asking, if some-one has information of speed of search, which one is faster, and how much it depend on number of lines?


Posted by Ivan Moala on November 11, 2000 4:04 AM

Without testing it I would have to say the Cells.find method would be faster.
The 1 to ? loop would have to be evaluated by
the VBA interpreter a number of times.....the
cell.find been a single call.....BUT i have not
tested this.........


Ivan