I thought some may be interested in the results of a speed test I ran to
verify some processing theories.
Machine:
HP
Intel Core 2
T7200 @ 2.00GHz,2.00GHz
2.00GB Ram
Softwa
XP sp2
Excel 2003

Scenario:
1 column of 10,000 numbers, 1 column of 10,000 letters, and 1 column of
randomly 10,000 numbers from the first column. The test was for various
lookup methods in VBA. Each was tested 3 times, listed are the middle times.

Test1a: pull each column into 2 seperate arrays, loop through 1 until the
answer is found then stop and go to the next. Time: 6.816 sec.

Test1b: pull each column into 2 seperate arrays, vlookup the data in the
second array and return the result. Time: 85.83 sec.

Test1c: insert a vlookup formula directly into the cell. Time: 6.093 sec.

Test1d: hybrid, put the vlookup in 1 cell with different criteria, then put
result in array. Time:12.94 sec.

Next I wanted to optimize the code for placing the formula directly into the
sheet;
using Application.Screenupdating=False the 6.093 sec was reduced to 5.953
sec. not too significant, but pairing that with
application.calculation=xlManual the time reduced to 2.296 seconds, a vast
improvement.

The second series pitted the best two methods (formula directly in the cell,
loop array against another array) against each other at 65536 cells.

Test2a: looping through one array to find a match method. Time: 301.855
Test2b: formula directly in cell. Time: 73.320

Conclusion: I had predicted from other posts on the matter that formula in
cell would be faster, but i didn't think the difference would be that large.
always turn off screenupdating and automatic calculation (don't forget to
turn it back on!). Any thoughts, or other methods are welcome.

looping method, 10000 results, 0.000681641 sec/result
looping method, 65536 results, 0.004605942 sec/result

direct method, 10000 results, 0.000229688 sec/result
direct method, 10000 results, 0.001118779 sec/result

--
-John
Please rate when your question is answered to help us and others know what
is helpful.