How to Loop

D

DTTODGG

Hello,
I have to run totals on many rows/columns.
I started a little macro, but would like to learn how to loop thru this data.

Please explain as you go, so I can learn rather than just copy your
excellent code :)

'Calculate Totals for every 15th row (3 in sample, 8 rows total)

Range("C2").Select
ActiveCell.FormulaR1C1 = "=SUM(R17C3,R32C3,R47C3,R63C3)"

Range("C3").Select
ActiveCell.FormulaR1C1 = "=SUM(R18C3,R33C3,R48C3,R64C3)"

Range("C4").Select
ActiveCell.FormulaR1C1 = "=SUM(R19C3,R34C3,R49C3,R65C3)"

'Move to next Column and calculate (2 in sample, 6 columns total)

Range("D2").Select
ActiveCell.FormulaR1C1 = "=SUM(R17C4,R32C4,R47C4,R63C4)"

Range("D3").Select
ActiveCell.FormulaR1C1 = "=SUM(R18C4,R33C4,R48C4,R64C4)"

Range("D4").Select
ActiveCell.FormulaR1C1 = "=SUM(R19C4,R34C4,R49C4,R65C4)"

Thanks in advance!
 
B

Bernie Deitrick

DTTODGG,

No need to loop - the formulas that you are using are the same in RC, so get
rid of the absolute addressing and enter it into all the cells at once:

Range("C2:D4").FormulaR1C1 = "=SUM(R[15]C,R[30]C,R[45]C,R[61]C)"

So, to enter your formulas in every column from C to Z:
Range("C2:Z4").FormulaR1C1 = "=SUM(R[15]C,R[30]C,R[45]C,R[61]C)"

And, by the way, your formula does NOT sum every fifteen row - the last cell
is off by one row from that pattern. If the pattern held, you could use a
different formula....

By the way, if you wanted to loop:

Sub Macro1()
Dim i As Integer
Dim j As Integer

For i = 2 To 4
For j = 3 To 26
Cells(i, j).FormulaR1C1 = "=SUM(R" & i + 15 & "C" & j & _
",R" & i + 30 & "C" & j & ",R" & i + 45 &
"C" & j & _
",R" & i + 61 & "C" & j & ")"
Next j
Next i
End Sub

HTH,
Bernie
MS Excel MVP
 
D

DTTODGG

Bernie,

Thank you for your quick reply.
I have made a typo and the rows are actually every 15 rows apart. The TOTAL
is on row 2. I'm adding row 17, 32, 47, 62, 77, etc.

I tried your formula, but, I must be doing something wrong - could you help?
I wanted to be able to read/verify my typing, so I would like to split/wrap
the lines of code. I get an error. See below...and again, thank you.

Range("C2:K9").FormulaR1C1 = "=SUM( _
R[17]C, _
R[32]C, _
R[47]C, _
R[62]C, _
R[77]C, _
R[92]C, _
R[107]C, _
R[122]C, _
R[137]C, _
R[152]C, _
R[167]C, _
R[182]C, _
R[197]C, _
R[212]C)"

'Bernie MS Excel MVP
 
D

Dave Peterson

Range("C2:K9").FormulaR1C1 = "=SUM(" _
& "R[17]C," _
& "R[32]C," _
& "R[47]C," _
& "R[62]C," _
& "R[77]C," _
& "R[92]C," _
& "R[107]C," _
& "R[122]C," _
& "R[137]C," _
& "R[152]C," _
& "R[167]C," _
& "R[182]C," _
& "R[197]C," _
& "R[212]C)"
Bernie,

Thank you for your quick reply.
I have made a typo and the rows are actually every 15 rows apart. The TOTAL
is on row 2. I'm adding row 17, 32, 47, 62, 77, etc.

I tried your formula, but, I must be doing something wrong - could you help?
I wanted to be able to read/verify my typing, so I would like to split/wrap
the lines of code. I get an error. See below...and again, thank you.

Range("C2:K9").FormulaR1C1 = "=SUM( _
R[17]C, _
R[32]C, _
R[47]C, _
R[62]C, _
R[77]C, _
R[92]C, _
R[107]C, _
R[122]C, _
R[137]C, _
R[152]C, _
R[167]C, _
R[182]C, _
R[197]C, _
R[212]C)"

'Bernie MS Excel MVP

Bernie Deitrick said:
DTTODGG,

No need to loop - the formulas that you are using are the same in RC, so get
rid of the absolute addressing and enter it into all the cells at once:

Range("C2:D4").FormulaR1C1 = "=SUM(R[15]C,R[30]C,R[45]C,R[61]C)"

So, to enter your formulas in every column from C to Z:
Range("C2:Z4").FormulaR1C1 = "=SUM(R[15]C,R[30]C,R[45]C,R[61]C)"

And, by the way, your formula does NOT sum every fifteen row - the last cell
is off by one row from that pattern. If the pattern held, you could use a
different formula....

By the way, if you wanted to loop:

Sub Macro1()
Dim i As Integer
Dim j As Integer

For i = 2 To 4
For j = 3 To 26
Cells(i, j).FormulaR1C1 = "=SUM(R" & i + 15 & "C" & j & _
",R" & i + 30 & "C" & j & ",R" & i + 45 &
"C" & j & _
",R" & i + 61 & "C" & j & ")"
Next j
Next i
End Sub

HTH,
Bernie
MS Excel MVP
Click to expand...
Click to expand...
 
D

DTTODGG

Dave,

Thanks for the "wrap".

Bernie,

I think I've got it. 15, 30, 45, 60, etc...
Is there a way to increment every 15th for however many "sets" of numbers
need totaling?

'Calculate

Range("C2:K9").FormulaR1C1 = "=SUM(R[15]C,R[30]C)"

Range("C12:K14").FormulaR1C1 = "=SUM(R[15]C,R[30]C)"



DTTODGG said:
Bernie,

Thank you for your quick reply.
I have made a typo and the rows are actually every 15 rows apart. The TOTAL
is on row 2. I'm adding row 17, 32, 47, 62, 77, etc.

I tried your formula, but, I must be doing something wrong - could you help?
I wanted to be able to read/verify my typing, so I would like to split/wrap
the lines of code. I get an error. See below...and again, thank you.

Range("C2:K9").FormulaR1C1 = "=SUM( _
R[17]C, _
R[32]C, _
R[47]C, _
R[62]C, _
R[77]C, _
R[92]C, _
R[107]C, _
R[122]C, _
R[137]C, _
R[152]C, _
R[167]C, _
R[182]C, _
R[197]C, _
R[212]C)"

'Bernie MS Excel MVP





Bernie Deitrick said:
DTTODGG,

No need to loop - the formulas that you are using are the same in RC, so get
rid of the absolute addressing and enter it into all the cells at once:

Range("C2:D4").FormulaR1C1 = "=SUM(R[15]C,R[30]C,R[45]C,R[61]C)"

So, to enter your formulas in every column from C to Z:
Range("C2:Z4").FormulaR1C1 = "=SUM(R[15]C,R[30]C,R[45]C,R[61]C)"

And, by the way, your formula does NOT sum every fifteen row - the last cell
is off by one row from that pattern. If the pattern held, you could use a
different formula....

By the way, if you wanted to loop:

Sub Macro1()
Dim i As Integer
Dim j As Integer

For i = 2 To 4
For j = 3 To 26
Cells(i, j).FormulaR1C1 = "=SUM(R" & i + 15 & "C" & j & _
",R" & i + 30 & "C" & j & ",R" & i + 45 &
"C" & j & _
",R" & i + 61 & "C" & j & ")"
Next j
Next i
End Sub

HTH,
Bernie
MS Excel MVP
Click to expand...
Click to expand...
 
B

Bernie Deitrick

Two easy ways spring to mind: (change the 10000s to a number that is at
least greater than your highest row)

Add a new column A, and use 1,2,3, etc for each set. Then use this in D2:L9
(what used to be C2)

=SUMIF($A$19:$A$10000,ROWS($A$1:A1),D$19:D$10000)

Or, array enter (enter using Ctrl-Shift-Enter) this formula in C2

=SUM(IF(MOD(ROW(C$19:C$10000)-ROWS(C$1:C4),15)=0,C$19:C$10000))

and copy to C2:K9.

For the VBA code, use the macro recored

HTH,
Bernie
MS Excel MVP


DTTODGG said:
Dave,

Thanks for the "wrap".

Bernie,

I think I've got it. 15, 30, 45, 60, etc...
Is there a way to increment every 15th for however many "sets" of numbers
need totaling?

'Calculate

Range("C2:K9").FormulaR1C1 = "=SUM(R[15]C,R[30]C)"

Range("C12:K14").FormulaR1C1 = "=SUM(R[15]C,R[30]C)"



DTTODGG said:
Bernie,

Thank you for your quick reply.
I have made a typo and the rows are actually every 15 rows apart. The
TOTAL
is on row 2. I'm adding row 17, 32, 47, 62, 77, etc.

I tried your formula, but, I must be doing something wrong - could you
help?
I wanted to be able to read/verify my typing, so I would like to
split/wrap
the lines of code. I get an error. See below...and again, thank you.

Range("C2:K9").FormulaR1C1 = "=SUM( _
R[17]C, _
R[32]C, _
R[47]C, _
R[62]C, _
R[77]C, _
R[92]C, _
R[107]C, _
R[122]C, _
R[137]C, _
R[152]C, _
R[167]C, _
R[182]C, _
R[197]C, _
R[212]C)"

'Bernie MS Excel MVP





Bernie Deitrick said:
DTTODGG,

No need to loop - the formulas that you are using are the same in RC,
so get
rid of the absolute addressing and enter it into all the cells at once:

Range("C2:D4").FormulaR1C1 = "=SUM(R[15]C,R[30]C,R[45]C,R[61]C)"

So, to enter your formulas in every column from C to Z:
Range("C2:Z4").FormulaR1C1 = "=SUM(R[15]C,R[30]C,R[45]C,R[61]C)"

And, by the way, your formula does NOT sum every fifteen row - the last
cell
is off by one row from that pattern. If the pattern held, you could
use a
different formula....

By the way, if you wanted to loop:

Sub Macro1()
Dim i As Integer
Dim j As Integer

For i = 2 To 4
For j = 3 To 26
Cells(i, j).FormulaR1C1 = "=SUM(R" & i + 15 & "C" & j & _
",R" & i + 30 & "C" & j & ",R" & i +
45 &
"C" & j & _
",R" & i + 61 & "C" & j & ")"
Next j
Next i
End Sub

HTH,
Bernie
MS Excel MVP
Click to expand...
Click to expand...
Click to expand...
 
B

Bernie Deitrick

And there are two non-formula methods that I should also mention:

Put 1,2,3, etc. in column A, then sort based on column A, and use Subtotals
based on changed values in column A.

Put 1,2,3, etc. in column A, then use a pivot table on all your data, using
Column A as the row data,and the data that you are summing as the data data.

HTH,
Bernie
MS Excel MVP


Bernie Deitrick said:
Two easy ways spring to mind: (change the 10000s to a number that is at
least greater than your highest row)

Add a new column A, and use 1,2,3, etc for each set. Then use this in
D2:L9 (what used to be C2)

=SUMIF($A$19:$A$10000,ROWS($A$1:A1),D$19:D$10000)

Or, array enter (enter using Ctrl-Shift-Enter) this formula in C2

=SUM(IF(MOD(ROW(C$19:C$10000)-ROWS(C$1:C4),15)=0,C$19:C$10000))

and copy to C2:K9.

For the VBA code, use the macro recored

HTH,
Bernie
MS Excel MVP


DTTODGG said:
Dave,

Thanks for the "wrap".

Bernie,

I think I've got it. 15, 30, 45, 60, etc...
Is there a way to increment every 15th for however many "sets" of numbers
need totaling?

'Calculate

Range("C2:K9").FormulaR1C1 = "=SUM(R[15]C,R[30]C)"

Range("C12:K14").FormulaR1C1 = "=SUM(R[15]C,R[30]C)"



DTTODGG said:
Bernie,

Thank you for your quick reply.
I have made a typo and the rows are actually every 15 rows apart. The
TOTAL
is on row 2. I'm adding row 17, 32, 47, 62, 77, etc.

I tried your formula, but, I must be doing something wrong - could you
help?
I wanted to be able to read/verify my typing, so I would like to
split/wrap
the lines of code. I get an error. See below...and again, thank you.

Range("C2:K9").FormulaR1C1 = "=SUM( _
R[17]C, _
R[32]C, _
R[47]C, _
R[62]C, _
R[77]C, _
R[92]C, _
R[107]C, _
R[122]C, _
R[137]C, _
R[152]C, _
R[167]C, _
R[182]C, _
R[197]C, _
R[212]C)"

'Bernie MS Excel MVP





:

DTTODGG,

No need to loop - the formulas that you are using are the same in RC,
so get
rid of the absolute addressing and enter it into all the cells at
once:

Range("C2:D4").FormulaR1C1 = "=SUM(R[15]C,R[30]C,R[45]C,R[61]C)"

So, to enter your formulas in every column from C to Z:
Range("C2:Z4").FormulaR1C1 = "=SUM(R[15]C,R[30]C,R[45]C,R[61]C)"

And, by the way, your formula does NOT sum every fifteen row - the
last cell
is off by one row from that pattern. If the pattern held, you could
use a
different formula....

By the way, if you wanted to loop:

Sub Macro1()
Dim i As Integer
Dim j As Integer

For i = 2 To 4
For j = 3 To 26
Cells(i, j).FormulaR1C1 = "=SUM(R" & i + 15 & "C" & j & _
",R" & i + 30 & "C" & j & ",R" & i +
45 &
"C" & j & _
",R" & i + 61 & "C" & j & ")"
Next j
Next i
End Sub

HTH,
Bernie
MS Excel MVP
Click to expand...
Click to expand...
Click to expand...
 

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

Macro Loop 1
How to write a for loop to get the range between a ,b columns 4
Need to turn this in to VBA 3
Looping in a range 3
Need to tailor my macro code. 9
Need to Sum all values above 4
loop to find text and place text in adjoining cell 4
subtotalling records 7

Top