Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with an array and SUMPRODUCT | Excel Worksheet Functions | |||
Sumproduct array | Excel Worksheet Functions | |||
Array or SumProduct or other? | Excel Worksheet Functions | |||
CountU from an array - SUMPRODUCT, SUM(IF(...)), both? | Excel Programming | |||
Another SUMPRODUCT array anomaly | Excel Worksheet Functions |