Hi Martin, thanks a lot for your reply.
As I'm fairly new to the whole VBA game, I don't think I could say
that I was proficient to add things to people's code (rather I can
copy it and make it work on whatever I'm working on!). Any quick
hints and I reckon I should be okay to get experimenting, but the
last thing I want to do is to start messing up the code!
Matt
On Jan 8, 12:18*pm, Martin Brown
wrote:
Matt Knight wrote:
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:
You might get some mileage by factoring out common terms and working
them out using VBA Application.WorksheetFunction.SumProduct()
It looks like you are enabling recalculation inside the inner loop which
will definitely hurt performance.
Different ways of indexing ranges have noticeably different overheads so
it is worth experimenting. The form you have chosen may be one of the
slower variants. Again it might be worth trying working on an explicit
memory array copy and copying the final numbers back to the spreadsheet.
BTW your long constant formulae are a maintainence trap with three
nearly identical versions differing in tiny ways. You should simplify
them to one common expression with the differences made explicit.
Regards,
Martin Brown
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