EVALUATE SUMPRODUCT formula

To TOPPERS and Bob Philips.

How can code 1 be amended to incorporate the EVALUATE option
(code 2) for the SUMPRODUCT function only. Thank you.

Code 1
Dim i As Long For i = 10201 To 10320 Step 8
Cells(i, "J").Resize(7).FormulaR1C1 = _
"=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10200C),R6C32:R10006C32)"
Cells(i + 7, "J").FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)" 'TOTAL FOR GROUP
Next i
'================
Code 2 EVALUATE
Range("d1") =
Evaluate("SUMPRODUCT(--(ALVXXL01!$B$2:$B$65536=A5),--(ALVXXL01!$K$2:$K$65536=""MMV3""),--(ALVXXL01!$P$2:$P$65536>0),--(ALVXXL01!$P$2:$P$65536))")

--
Robert

dblVal = Evaluate("SUMPRODUCT(--(R6C6:R10006C6=RC9)" & _
",--(R6C4:R10006C4=R10200C),R6C32:R10006C32)")

Since all your references are absolute, you would only need to do this once.

--
Regards,
Tom Ogilvy


"Robert" <[email protected]> wrote in message
news:[email protected]...
> To TOPPERS and Bob Philips.
>
> How can code 1 be amended to incorporate the EVALUATE option
> (code 2) for the SUMPRODUCT function only. Thank you.
>
> Code 1
> Dim i As Long For i = 10201 To 10320 Step 8
> Cells(i, "J").Resize(7).FormulaR1C1 = _
>
"=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10200C),R6C32:R10006C32
)"
> Cells(i + 7, "J").FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)" 'TOTAL FOR GROUP
> Next i
> '================
> Code 2 EVALUATE
> Range("d1") =
>
Evaluate("SUMPRODUCT(--(ALVXXL01!$B$2:$B$65536=A5),--(ALVXXL01!$K$2:$K$65536
=""MMV3""),--(ALVXXL01!$P$2:$P$65536>0),--(ALVXXL01!$P$2:$P$65536))")
>
> --
> Robert

Thank you Tom, could you assist me with exact code(believe me, I tried many
times but without success,I do not know any VBA).
--
Robert

I appended the code with recorded a macro code which copies the range and
PasteSpecialValues. Achieved what I desired.
--
Robert