Why not to use a Access Table to store all of your data and use either DAO
or ADO to retieve by SQL statements only the recordsets to work with, that
way you keep integrity on your data.
"Random" <Random@nwhere wrote in message
...
Thanks for the response. That sped the process up a bit.

Random


On Wed, 27 Aug 2003 17:21:19 -0700, "KevinB"
wrote:

The screen updating issue I can help you with, the rest I
don't know.

Application.ScreenUpdating = False
... other code ...
Application.ScreenUpdating = True

KB

-----Original 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
.