Is there a way to speed up Calculation !!!!!!!!!!!!!!!
in [Programming]
|
From: Ayo on 29 Apr 2010 15:15 I have this line of code in my macro: Wks.Range("G3:GA732").Calculate This line of code takes over an hour to execute. The range is all SUMPRODUCT formulae and I have to run the report daily. It takes about 2 hours to run the report. 90% of that is spent executing the "Calculate" code above. What I need to know is this, is there a way to speed-up the Calculating operation or am I stuck with this? Thanks
From: Gary Brown on 29 Apr 2010 15:36 physical limitations get more memory and/or faster cpu -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Ayo" wrote: > I have this line of code in my macro: Wks.Range("G3:GA732").Calculate > > This line of code takes over an hour to execute. The range is all > SUMPRODUCT formulae and I have to run the report daily. It takes about 2 > hours to run the report. 90% of that is spent executing the "Calculate" code > above. > > What I need to know is this, is there a way to speed-up the Calculating > operation or am I stuck with this? > Thanks
From: Peter T on 29 Apr 2010 16:07 Range("G3:GA732"): Are you really saying you've got 129210 formulas with SumProduct to calculate, and in turn who knows what else in the formula. Regards, Peter T "Ayo" <Ayo(a)discussions.microsoft.com> wrote in message news:789BD9AE-DA96-4DE3-9938-0BC42AE82736(a)microsoft.com... > I have this line of code in my macro: Wks.Range("G3:GA732").Calculate > > This line of code takes over an hour to execute. The range is all > SUMPRODUCT formulae and I have to run the report daily. It takes about 2 > hours to run the report. 90% of that is spent executing the "Calculate" > code > above. > > What I need to know is this, is there a way to speed-up the Calculating > operation or am I stuck with this? > Thanks
From: Tom Hutchins on 29 Apr 2010 18:17 I have read occasionally that SUMIF is much faster than SUMPRODUCT. Substituting SUMIF where possible may cut your recalculation time. This link is all about optimization: http://www.decisionmodels.com/ Hope this helps, Hutch "Ayo" wrote: > I have this line of code in my macro: Wks.Range("G3:GA732").Calculate > > This line of code takes over an hour to execute. The range is all > SUMPRODUCT formulae and I have to run the report daily. It takes about 2 > hours to run the report. 90% of that is spent executing the "Calculate" code > above. > > What I need to know is this, is there a way to speed-up the Calculating > operation or am I stuck with this? > Thanks
|
Pages: 1 Prev: Setting up a macro that can be ran from any excel file. Next: referring cell in macro function |