Thanks Joel - really appreciate it. I'll have a play around now and
see what happens!

Matt

On Jan 8, 2:09*pm, joel wrote:
Sumproduct formulas are known to slow down a workbook considerable.
Putting ther formula into VBA the way you did doesn't change the speed.
what you can do is replace the formula with a value. *You can use the
evaluate method in ?VBA like this

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))"
Results = Evaluate(formula1)

The evaluate still takes the same amount of time to execute as putting
the formula in the worksheet but the calculation is only performed once,
not every time the workbook is updated.

All I did was to remove the equal sign from you formula and added
parenthesis around you code.

you had
Formula1 = "=........."

I replaced it with
Formula1 = "........."

Results = evaluate(Formula1)

to make you formula more versitile what I do is add ranges like this

with sheets("Course List by division'")

Set MyRange1 = .Range("$C6:$C$607")
MyRangeAddr1 = MyRange1.address(external:=true)

Set MyRange2 = .Range("$I6:$I$607")
MyRangeAddr2 = MyRange2.address(external:=true)

Set MyRange3 = .Range("$E6:$E$607")
MyRangeAddr3 = MyRange3.address(external:=true)

Set MyRange4 = .Range("$J6:$J$607")
MyRangeAddr4 = MyRange3.address(external:=true)

Formula1 = "if(sumproduct(--(" & MyRangeAddr4 & "=E$4)," & _
"--(" & MyRangeAddr2 & "=0,0," &
_
"sumproduct(--(" & MyRangeAddr3 & "=$b6)," & _
"--(" & MyrangeAddr1 & "=E$4)," &
_
MyRangeAddr2 & ")/" &
_
"sumproduct(--(" & MyRangeAddr1 & "=E$4)," & _
MyRangeAddr2 & "))"

I didn't test my changes but they should be close.

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=167924

Microsoft Office Help