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

Thank
Cory

Hi
not tested but after pasting/autofill the formulas insert the line
application.calculate


--
Regards
Frank Kabel
Frankfurt, Germany

"Cory" schrieb im Newsbeitrag
...
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

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
.