View Full Version : Solved: Problem with For Loop
newbie2009
03-12-2008, 08:51 PM
Hi i have an excel workbook with many sheets some of which have table formulas. Pressing F9 takes a long time to calculate. I think VBA can help but i am not sure why the following code would not work (doesnt loop through the next sheets after the first).
Sub Calculate()
Dim MySheet As Worksheet
Dim Counter As Integer
For Each MySheet In ActiveWorkbook.Sheets
ActiveSheet.Calculate
Next MySheet
End Sub
Appreciate any help
Trevor
03-12-2008, 09:28 PM
what error(s) are you getting and/or what is happening when you say this isn't working?
gwkenny
03-12-2008, 10:19 PM
Hi:
First point with regards to your code: You don't want to say ActiveSheet.Calculate. The code WILL go through all the sheets in the workbook, but only the ACTIVE sheet will be recalculated. You want the code changed to:
For Each MySheet In ActiveWorkbook.Sheets
MySheet.Calculate
Next MySheet
Hopefully you can follow that explanation, if not, please let me know and I will elaborate further.
Second point with regards to calculation: Choosing calculate through vba will not make your spreadsheet calculate faster than F9.
You need to examine your spreadsheet regarding the organization of data and the functions used. Especially recursive functions (array formulas etc...) and for redundant formulas.
newbie2009
03-12-2008, 11:11 PM
Thanks. it's working now after i made the changes you suggested
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.