Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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$655360),--(ALVXXL01!$P$2:$P$65536))") -- Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
EVALUATE SUMPRODUCT formula
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" wrote in message ... 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$655360),--(ALVXXL01!$P$2:$P$65536))") -- Robert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
EVALUATE SUMPRODUCT formula
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
EVALUATE SUMPRODUCT formula
I appended the code with recorded a macro code which copies the range and
PasteSpecialValues. Achieved what I desired. -- Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Will Not Evaluate | Excel Worksheet Functions | |||
Why won't Sumproduct funciton evaluate this data | Excel Worksheet Functions | |||
Evaluate sumproduct question | Excel Programming | |||
Evaluate formula using VBA | Excel Discussion (Misc queries) | |||
Evaluate - Sumproduct GRRR | Excel Programming |