Another option would be to run loops in arrays. Look at the following
posting:

http://groups.google.com/groups?hl=e...r%3D%26hl%3Den

If this link doesn't work do a search for Lookup, RB Smissaert in this
group.


RBS

"Random" <Random@nwhere wrote in message
...
Does anyone have any advice on how to solve a problem with VLOOKUPS
and slow computers?

Problem: Large spreadsheet (35,000 rows, 15 columns Static) and
medium spreadsheet (4500 rows, 4 columns and growing). The smaller
spreadsheet needs to pull matching information from the larger sheet.
However, having VLOOKUP in all of the rows caused the comp to crawl
(5-10 minutes to add a cell). I tried turning off Auto-Calculate, it
helped, but when I updated..... stalled again.

My initial solution was to create a Worksheet_Change sub that
temporarily inserted the VLOOKUP formula into the cells on the row
that data had been entered into and then copy/paste special-values.

My thought was that the lack of numerous VLOOKUPS would speed up the
entire sheet, which it did.

ok.. so now to the questions: This seems like a rather cumbersome way
to accomplish this task.

1. Does anyone have any suggestions on how to accomplish the same
thing without resorting to inserting formulas and then pasting over
them?

2. Is there a way to turn off the screen update during a sub? So
that the user cannot see the cells being updated, but only the final
results?

Thanks to everyone for their time and thoughts.

Random