Sumproduct, array
 

Could someone help me in modifying the following code running on Excel XP.
1. substitute RC9 with an array "P1","P1D","X1","P1S","U1","A1","X1D" (none
are cell references)
2. Instead of .FormulaR1C1, change to EvaluateR1C1 (to make the lower half
of the code redundant).
3. if the existing code is not to be changed how should I enter the above
array in RC9 onwards on the worksheet to cater for other selections.

Ps. Next to zero knowledge of VBA , I had referred to previous posts but was
not able to adapt any.
Thank you.

Sub Perf1()
ActiveWindow.ActivateNext
Application.Goto Reference:="SALES"
Dim i As Long
For i = 10068 To 10179 Step 8
Cells(i, "J").Resize(7).FormulaR1C1 = _
"=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C5:R10006C5=R10067C),R6C25:R10006C25*RC7)"
Cells(i + 7, "J").FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
Next i

Range("J10068:J10179").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
--
Robert

Sumproduct, array
 

Seem to have got it. Placed the formula in the first cell. Start record
macro, Press F2 ENTER, Stop record macro. Gave
"=SUMPRODUCT((R6C6:R10006C6={""P1"",""P1D"",""X1"" ,""P1S"",""U1"",""A1"",""X1D""})*((R6C4:R10006C4)= RC[-2])*(R6C25:R10006C25))"
which I substituted on the original code.
--
Robert


"Robert" wrote:

Could someone help me in modifying the following code running on Excel XP.
1. substitute RC9 with an array "P1","P1D","X1","P1S","U1","A1","X1D" (none
are cell references)
2. Instead of .FormulaR1C1, change to EvaluateR1C1 (to make the lower half
of the code redundant).
3. if the existing code is not to be changed how should I enter the above
array in RC9 onwards on the worksheet to cater for other selections.

Ps. Next to zero knowledge of VBA , I had referred to previous posts but was
not able to adapt any.
Thank you.

Sub Perf1()
ActiveWindow.ActivateNext
Application.Goto Reference:="SALES"
Dim i As Long
For i = 10068 To 10179 Step 8
Cells(i, "J").Resize(7).FormulaR1C1 = _
"=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C5:R10006C5=R10067C),R6C25:R10006C25*RC7)"
Cells(i + 7, "J").FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
Next i

Range("J10068:J10179").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
--
Robert