Excel fill down formula recalculation question


D

Dick

I open around 150 spreadsheets and run a macro that fills down the data
on the row that the cursor is on down to the next row and then moves the
cursor down to the appropriate location. There are some formulas that
get recalculated just because of the nature of the fill down to the next
line.

On my old Compaq Celeron 433 with 96 mb of memory running Excel 2000 it
only took 26 seconds to cycle through all of the spreadsheets and do the
fill down and recalculations. Now with my new Dell Celeron 2700 with
1024 mb of memory running Excel 2003, it takes 35 seconds to run. I
don't understand why it takes longer on a faster machine with more
memory. I went through and checked all of the possible performance
explanations on the MVP dmvritchie web cite and another besides and no
explanation seemed to explain why my visual basic function should run
slower on a faster machine with more memory. Anyway I set calculation to
manual while I cycled through the spreadsheets doing the fill down and
then at the end turned calculation back onto automatic and the macro
only took 18 seconds to finish which is more in line with what I was
expecting to see in the first place. So I don't understand how to
explain this discrepancy unless there is some kind of conflict between
the first fill down and recalculation and then having visual basic move
to the next spreadsheet and start doing the next fill down operation
again and possibly the next calculation on that spreadsheet as opposed
to doing all of the fill downs and then doing the formuala recalculation
at the end. If I remember right didn't Microsoft change some things in
Excel 2003 so that calculated numbers would be more accurate to a
smaller decimal place?

Thanks Dick
 
Ad

Advertisements

B

BrianB

FWIW I have discovered that Copy/Paste methods work faster than cycling
cells - especially if formats are required.
 
Ad

Advertisements

D

Dick

It would seem counter intuitive to expect that copying to the clipboard
and then pasteing onto the next line would be faster so I never even
thought to try that. Thanks for the suggestion. I will give it a try.

DickN
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top