My apologies for the radio silence on this - been busy on other jobs
with limited internet connectivity.
But rest assured myself and a colleague have been scratching our heads
over this for the last week on and off. We've essentially merged a
few of the methods that have been suggested and are just trying to
iron out some small problems. Hopefully we'll have cracked it by the
end of today. EIther way I'll keep you posted and post the final
solution for anyone else to steal.
Matt
On Jan 8, 2:39*pm, Matt Knight wrote:
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- Hide quoted text -
- Show quoted text -