SUMPRODUCT Formula in VBA

Question

I want to use following SUMPRODUCT formula in VBA:

=SUMPRODUCT((Sale!$J$5:$J$1048576=C12)*Sale!$D$5:$D$1048576,Sale!$M$5:$M$1048576)

I used this code but it gives an error

Run-time error '13': Type mismatch

Dim slWks As Worksheet

Set slWks = Sheets("Sale")
ActiveSheet.Range("F12").Value = _
Application.WorksheetFunction.SumProduct((slWks.Range("J5:J1048576") = _
    ActiveSheet.Range("C12")) * slWks.Range("D5:D1048576"), slWks.Range("M5:M1048576"))

How can I write that formula with its values using vba?

Answer 1

Two possible simple solutions, given that worksheetfunction methods won't work with arrays the size that you are using:

First, add the formula and then replace it with its value

With activesheet.Range("F12")
   .Formula =" =SUMPRODUCT((Sale!$J$5:$J$1048576=C12)*Sale!$D$5:$D$1048576,Sale!$M$5:$M$1048576)"
   .Value2 = .Value2
End With

Second, use Evaluate:

With Activesheet
   .range("F12").Value2 = .Evaluate("SUMPRODUCT((Sale!$J$5:$J$1048576=C12)*Sale!$D$5:$D$1048576,Sale!$M$5:$M$1048576)")
End With