Try:
Application.screenupdating=false
This should speed the macro up
Also, rather than using a lookup, wouldn't a free-wheeling
macro be able to do what you need?
If you would like to send a dummy sheet to me, say about
10 line, (up to 1mb), maybe I could write something. See
http://au.geocities.com/excelmarksway for some of my
handiwork.



-----Original Message-----
I have a spreadsheet with about 14,000-17000 rows and 9
columns that i have to have a vlookup happening in. It
pulls data from a seperate sheet.

The problem is that process takes about 10-40minutes
depending on the computer it is run on, and my job is to
make those vlookups happen in each column, then paste
special values to get the actual values in the cells.
Right now i have the vlookup (the formula works) in row 5
column X (have 9 columns to run these on) and i find the
final row, and i have to copy row5 columnX and paste from
row6 colX to rowFinal colX.

So i figured the best plan of attack would be to do
a .FillDown and then after it did that, copy the selection
and do a Selection.PasteSpecial xlValues and that would be
good. The only problem is that it doesnt want to
calculate the values before i copy and paste special, so i
only get empty cells when they should all be full.

My question is this:
How can I get my script to pause till the vlookups are
done, then copy and paste special?

Thanks
Cory
.