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

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