Hi Gary,

Thanks for the response - I'm not sure that doing what you've
suggested would have a massive impact, unless I've misunderstood what
you mean. Seeing as I have to calculate 172000 fairly complex
formulas, would it make much of a difference as to when the calcs are
done?

Matt

On Jan 7, 9:29*pm, "Gary Keramidas" <GKeramidasAtMSN.com wrote:
just a thought, not sure what it would do because i don't know all of the
implications, but maybe just calculating the relevant cells instead of *global
recalc. then turn calculation back on at the end, instead of the end of every
loop.

--

Gary Keramidas
Excel 2003

"Matt Knight" wrote in message

...

This started off over in microsoft.public.excel:
http://groups.google.com/group/micro...rowse_thread/t...

Basically I was using a really inefficient macro to run some calcs and
paste in values to restrict file size. *Luckily, Otto kindly managed
to sort out some more efficient code, but as there are still 172,000
calcs, so obviously they take some time (on the machine I need tom to
work well on, they take about 9mins). *Here's the latest code I'm
using:

Sub driver_calc()
* *Application.ScreenUpdating = False
* *Dim myLC As Long
* *Dim myLR As Long
* *Dim myLRr As Long
* *Dim myLCr As Long
* *Dim ws As Worksheet
* *Dim TheFormula As String

* *Const Formula1 = "=if(sumproduct(--('Course List by division'!$c
$6:$C$607=E$4),'Course List by division'!$i$6:$i$607)=0,0,sumproduct(--
('Course List by division'!$E$6:$e$607=$b6),--('Course List by
division'!$c$6:$c$607=E$4),'Course List by division'!$i$6:$i$607)/
sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List
by division'!$i$6:$i$607))"

* *Const Formula2 = "=if(sumproduct(--('Course List by division'!$c
$6:$C$607=E$4),'Course List by division'!$j$6:$j$607)=0,0,sumproduct(--
('Course List by division'!$E$6:$e$607=$b6),--('Course List by
division'!$c$6:$c$607=E$4),'Course List by division'!$j$6:$j$607)/
sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List
by division'!$j$6:$j$607))"

* *Const Formula3 = "=if(sumproduct(--('Course List by division'!$c
$6:$C$607=E$4),'Course List by division'!$k$6:$k$607)=0,0,sumproduct(--
('Course List by division'!$E$6:$e$607=$b6),--('Course List by
division'!$c$6:$c$607=E$4),'Course List by division'!$k$6:$k$607)/
sumproduct(--('Course List by division'!$c$6:$c$607=E$4),'Course List
by division'!$k$6:$k$607))"

* *For Each ws In Sheets(Array("Driver 1 - STUDENTS", "Driver 2 -
TIME", "Driver 3 - STUDENTSxTIME"))
* * * *With ws
* * * * * *Application.Calculation = xlCalculationManual
* * * * * *myLC = .Range("IV4").End(xlToLeft).Column
* * * * * *myLR = .Cells(Rows.Count, "C").End(xlUp).Row
* * * * * *myLCr = .Range("IV6").End(xlToLeft).Column
* * * * * *myLRr = .Cells(Rows.Count, "E").End(xlUp).Row
* * * * * *.Range("E6", .Cells(myLR, myLC)).ClearContents
* * * * * *Select Case ws.Name
* * * * * * * *Case "Driver 1 - STUDENTS": TheFormula = Formula1
* * * * * * * *Case "Driver 2 - TIME": TheFormula = Formula2
* * * * * * * *Case "Driver 3 - STUDENTSxTIME": TheFormula = Formula3
* * * * * *End Select
* * * * * *.Range("E6").Formula = TheFormula
* * * * * *.Range("E6").Copy .Range("E6", .Cells(myLR, myLC))
* * * * * *Application.Calculation = xlCalculationAutomatic
* * * * * *.Range("E6", .Cells(myLR, myLC)).Copy
* * * * * *.Range("E6").PasteSpecial Paste:=xlPasteValues
* * * * * *.Application.CutCopyMode = False
* * * *End With
* *Next ws
* *MsgBox "Drivers succesfully updated", vbInformation,
"Lxxxxxxxxxxxxx"
End Sub

I was wandering if there was a better way of using the formulae rather
than getting Excel to do them "on the fly". *FYI, I'm using Excel 2003
on XP, but the client I'm doing this for uses Excel 2007 on XP, so
getting superfast on 03 isn't essential.

If anyone can offer any advice.guidance I'd massively appreciate it!

Cheers
Matt