| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping in a range
Calc mode Manual
J10140:AK10140= Dates I10141:I10449 = Sales types in groups J10141:AK10449 sumproduct formulas extracting data from a Database of 10000 rows. I recorded a macro by merely pressing F2 and ENTER for each cell from the start row to the last row. Sample here is only for row 10141 to row 10156. Actual is upto Row 10449 (25 pages in MSWord). Can someone comment on this and help me with e me a looping VBA code. Have not resorted to autofilter as the summarised Data need to follow through for other calculations. Thank you. ActiveCell.FormulaR1C1 = "=SalesSummary" Range("J10141").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10142").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10143").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10144").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10145").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10146").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10147").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10148").Select ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)" €˜TOTAL FOR GROUP Range("J10149").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10150").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10151").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10152").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10153").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10154").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10155").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10156").Select ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)" €˜TOTAL FOR GROUP -- Robert |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping in a range
Try this as starter. I am a bit lots as to what is in column F and D and
exactly what you formula does, it seems to repeat itself 7 times, and then sum that lot (?). I defined Startcell so that I could use something other than J1041 Dim startCell As String Dim iLastRow As Long startCell = "J10140" iLastRow = Cells(Rows.Count, "J").End(xlUp).Row Range(startCell).FormulaR1C1 = "=SalesSummary" For i = Range(startCell).Offset(1).Row To iLastRow Step 8 Cells(i, "J").Resize(7).FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Cells(i + 7, "J").FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)" 'TOTAL FOR GROUP Next i -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Robert" wrote in message ... Calc mode Manual J10140:AK10140= Dates I10141:I10449 = Sales types in groups J10141:AK10449 sumproduct formulas extracting data from a Database of 10000 rows. I recorded a macro by merely pressing F2 and ENTER for each cell from the start row to the last row. Sample here is only for row 10141 to row 10156. Actual is upto Row 10449 (25 pages in MSWord). Can someone comment on this and help me with e me a looping VBA code. Have not resorted to autofilter as the summarised Data need to follow through for other calculations. Thank you. ActiveCell.FormulaR1C1 = "=SalesSummary" Range("J10141").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10142").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10143").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10144").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10145").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10146").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10147").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10148").Select ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)" 'TOTAL FOR GROUP Range("J10149").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10150").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10151").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10152").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10153").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10154").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10155").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10156").Select ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)" 'TOTAL FOR GROUP -- Robert |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping in a range
Bob, thanks for responding. I did try your code, it works except that
due to my earlier mistake, the start row is not right. In reply D6:D10006=dates F6:F10006=Product codes AF6:AFY10006=Values My worksheet has over 2000 SUMPRODUCT formulas which slow Down the performance to an unbearable wait (about 15 minutes). Since I only need the calculation for 1 day at a time, I have set the workbook To manual calc. Thereafter I invoke the macro to calc the sumproduct formulas From J10141 to J10440 for the first date. The next day I will invoke a similar Macro to calc K10168 to K10440 What I am doing is manually calc each cell in a column By Pressing F2 followed by ENTER. The performance is much more Efficient except for the lengthy code of 25 pages for each day. I am sure A short VBA code with looping can achieve this. I have reproduced the Actual macro code, shortened for Range J10141 to J10159 (my apologies, the Earlier macro code had some transcribing errors) The number of product codes will vary from group to group ie. Groups 1 And 2 have 7 products, group 3 only 2. So there is no fixed number of rows for each group. MACRO CODE Application.Goto Reference:="R10141C10" ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10142").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10143").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10144").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10145").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10146").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10147").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10148").Select ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"TOTAL for Group 1 Range("J10149").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10150").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10151").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10152").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10153").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10154").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10155").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Range("J10156").Select ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)" Range("J10157").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C34:R10006C34)" Range("J10158").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C34:R10006C34)" Range("J10159").Select ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"TOTAL for group 2 Range("J10160").Select End Sub -- Robert "Bob Phillips" wrote: Try this as starter. I am a bit lots as to what is in column F and D and exactly what you formula does, it seems to repeat itself 7 times, and then sum that lot (?). I defined Startcell so that I could use something other than J1041 Dim startCell As String Dim iLastRow As Long startCell = "J10140" iLastRow = Cells(Rows.Count, "J").End(xlUp).Row Range(startCell).FormulaR1C1 = "=SalesSummary" For i = Range(startCell).Offset(1).Row To iLastRow Step 8 Cells(i, "J").Resize(7).FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Cells(i + 7, "J").FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)" 'TOTAL FOR GROUP Next i -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Robert" wrote in message ... Calc mode Manual J10140:AK10140= Dates I10141:I10449 = Sales types in groups J10141:AK10449 sumproduct formulas extracting data from a Database of 10000 rows. I recorded a macro by merely pressing F2 and ENTER for each cell from the start row to the last row. Sample here is only for row 10141 to row 10156. Actual is upto Row 10449 (25 pages in MSWord). Can someone comment on this and help me with e me a looping VBA code. Have not resorted to autofilter as the summarised Data need to follow through for other calculations. Thank you. ActiveCell.FormulaR1C1 = "=SalesSummary" Range("J10141").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10142").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10143").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10144").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10145").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10146").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10147").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10148").Select ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)" 'TOTAL FOR GROUP Range("J10149").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10150").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10151").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10152").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10153").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10154").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10155").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32 )" Range("J10156").Select ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)" 'TOTAL FOR GROUP -- Robert |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping in a range
Bob, By trial and error I managed to put together the following using your code
and other codes previously sourced. It seems to be working as I intended. Thank your your valuable assistance which I could not do without. I had to standardise the number rows to 7 even if there will be not values to be extracted. Dim i As Long For i = 10141 To 10260 Step 8 Cells(i, "J").Resize(7).FormulaR1C1 = _ "=SUMPRODUCT(--(R6C6:R10006C6=RC9),--(R6C4:R10006C4=R10140C),R6C32:R10006C32)" Cells(i + 7, "J").FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)" 'TOTAL FOR GROUP Next i ' End Sub -- Robert |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Looping through a range | Excel Programming | |||
| Looping through a range of cells | Excel Programming | |||
| looping across columns in range? | Excel Discussion (Misc queries) | |||
| help with looping range and extracting value | Excel Programming | |||
| looping through a range | Excel Programming | |||
Linear Mode
