Help with error 2015 in macro

[Matt Knight]

Hi everyone, I've been trying to sort this macro out for what seems
like an age now... Some progress has been made, and I'm hoping that
this nis the final hurdle! The problem is as follows:

Right, this is the first part of the code we've been trying to get to
work:

Sub driver_calc()

Application.ScreenUpdating = False

Dim myLC As Long
Dim myLR As Long
Dim myLRr As Long
Dim myLCr As Long

Sheets("Driver 1 - STUDENTS").Select

With Sheets("Driver 1 - STUDENTS")
Application.Calculation = xlCalculationManual
myLC = .Range("IV4").End(xlToLeft).Column
myLR = .Cells(Rows.Count, "B").End(xlUp).Row
myLCr = .Range("IV5").End(xlToLeft).Column
myLRr = .Cells(Rows.Count, "C").End(xlUp).Row
.Range("e6", .Cells(myLRr, myLCr)).ClearContents

Set MyRange1 = .Range("B5:B" & myLR)
MyRangeAddr1 = MyRange1.Address(external:=True)

Set MyRange2 = .Range("C4", .Cells(4, myLC))
MyRangeAddr2 = MyRange2.Address(external:=True)

End With

Formula1 = "if(sumproduct(--('Course List by division'!$c$6:$C
$607=" & MyRangeAddr2 & "),'Course List by division'!$i$6:$i$607)
=0,0,sumproduct(--('Course List by division'!$E$6:$e$607=" &
MyRangeAddr1 & "),--('Course List by division'!$c$6:$c$607=" &
MyRangeAddr2 & "),'Course List by division'!$i$6:$i$607)/sumproduct(--
('Course List by division'!$c$6:$c$607=" & MyRangeAddr1 & "),'Course
List by division'!$i$6:$i$607))"

'"" & MyRangeAddr1 & " * " & MyRangeAddr2 & ""

Result1 = Evaluate(Formula1)

Sheets("Driver 1 - STUDENTS").Range("c5", Cells(myLR,
myLC)).Formula = Result1

The issue we're having is that we get an error 2015 producing
"#VALUE!" in all the cells in Excel. Have seen a couple of examples
of 2015s elsewhere but not figured out a solution to
this problem (if there is one?!)

If anyone could offer any advice I'd massively appreciate it. Give me
a shout if you need more info

Cheers
Matt

 

[joel]

I think you were missing some closing parenthsis. It would be better i
you wrote the formula like this which would be easier to debug

sprod1 = Evaluate("sumproduct(--('Course List b
division'!$c$6:$C$607=" & MyRangeAddr2 & ")," & _
"'Course List by division'!$i$6:$i$607)")

If sprod1 = 0 Then
Results = 0
Else
sprod2 = Evaluate("sumproduct(--('Course List b
division'!$E$6:$e$607=" & MyRangeAddr1 & ")," & _
"--('Course List by division'!$c$6:$c$607= " & MyRangeAddr2
")," & _
"'Course List by division'!$i$6:$i$607)")
Results = sprod1 / sprod2
End If


If this still gives you an error here is a trick I use

Create a string like this

TestStr = "sumproduct(--('Course List by division'!$c$6:$C$607=" & _
MyRangeAddr2 & ")," & "'Course List by division'!$i$6:$i$607)"

The write the string (notice no equal sign) to the worksheet lik
this

Range("A1") = TestStr


Now go to the worksheet and add an equal sign in front of the string.
You will get an error on the worksheet but excel on the workheet wil
let you know where the error is in the string or even correct the erro
for you