Looping in a range

R

Robert

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
 
B

Bob Phillips

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 said:
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
Click to expand...
 
R

Robert

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 said:
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 said:
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
Click to expand...
Click to expand...
 
R

Robert

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

EVALUATE SUMPRODUCT formula 3
How to chain VBA applications 5

Top