Sheet.Calculate limits??
 

Hello,
I have a macro that does the following (Excel 2000):
Sheets("MySheet").select
ActiveSheet.EnableCalculation = False

<Write 1000's of v-lookup formulas in sheet
<e.g. i have one instance with ~ 12000 formulas

ActiveSheet.EnableCalculation = True
ActiveSheet.Calculate

<Overwrite all formulas with values through copy &
paste-values commands...


The problem: the last 20-30 rows (about 400-600 formulas)
do not calculate before the code proceeds with the
overwrite.
How can this happen?
How can it be fixed?

Thanks,
Luz

Sheet.Calculate limits??
 

Strange. This happens when connecting Excel to external applications bu
I have not come across this problem. The macro runs faster than th
application can complete the process.

A fix is supposed to be to add the line
DoEvents
in your case, after the calculate command, but I have never found thi
to work very well - so, in addition, ("belt and braces") I add a Wai
command to stop the macro working for a few seconds, tweaking th
amount of time to suit. eg. 4 seconds
Application.Wait Now + TimeValue("00:00:04")

Hope this helps

--
Message posted from http://www.ExcelForum.com

Sheet.Calculate limits??
 

Thanks very much!
My program does not interact with any other applications,
so I have been quite puzzled by this. I thought about the
wait command, but though I'd ask to see if there was
something better. I guess not... But the DoEvents is a
new one for me and so I'll try it. Thanks so much!
Luz


-----Original Message-----
Strange. This happens when connecting Excel to external
applications but
I have not come across this problem. The macro runs
faster than the
application can complete the process.

A fix is supposed to be to add the line
DoEvents
in your case, after the calculate command, but I have
never found this
to work very well - so, in addition, ("belt and braces")
I add a Wait
command to stop the macro working for a few seconds,
tweaking the
amount of time to suit. eg. 4 seconds
Application.Wait Now + TimeValue("00:00:04")

Hope this helps.


---
Message posted from http://www.ExcelForum.com/

.