Add up, subtotals then totals

[Buffyslay]

currently my code does this


Range("E9").Activate
'f9 is the first value - Jan so
'move across cols to correct month (n)

ActiveCell.Offset(0, n).Select
ActiveCell.Offset(12, 0).Select
'this is subtotal number one - the string contains 'SUBTOTAL'
ActiveCell.FormulaR1C1 = "=Sum(R[-1]C:R[-12]C)"
intTotal = intTotal + ActiveCell.Value

'move down
ActiveCell.Offset(33, 0).Select
'this is subtotal number two - the string contains 'SUBTOTAL'
ActiveCell.FormulaR1C1 = "=Sum(R[-1]C:R[-31]C)"
intTotal = intTotal + ActiveCell.Value

ActiveCell.Offset(8, 0).Select
'this is subtotal number three - the string contains 'SUBTOTAL'
ActiveCell.FormulaR1C1 = "=Sum(R[-1]C:R[-7]C)"
intTotal = intTotal + ActiveCell.Value
ActiveCell.Offset(2, 0).Select

'this is the main total
ActiveCell.Value = intTotal

Range("E9").Activate
ActiveCell.Offset(0, n).Select
move across cols to correct month (n)

For j = 1 To 66
intTotal = ActiveCell.Value
ActiveCell.Value = intTotal
ActiveCell.Offset(1, 0).Select
Next


************************

what i want to do is!
move to E9 (first cell) add up the cells in the cols in the right
month, until you hit a subtotal. there is a blank line under each
subtotal.

Then hold the subtotal value then put it into the main total....

having blonde day

thanks
Pennie
http://www.buffyslay.co.uk

 

[Dave Peterson]

I'm not sure what you're doing, but have you thought about using excel's built
in Data|subtotals?

You could record a macro when you did it once.

In fact, to get to bold, continue recording while you use the outline symbols to
hide the rows that aren't subtotals.

Then select that visible range
Then hit Edit|Goto|special
click on Visible
apply your bolding

You'll have your code for that, too.

currently my code does this

Range("E9").Activate
'f9 is the first value - Jan so
'move across cols to correct month (n)

ActiveCell.Offset(0, n).Select
ActiveCell.Offset(12, 0).Select
'this is subtotal number one - the string contains 'SUBTOTAL'
ActiveCell.FormulaR1C1 = "=Sum(R[-1]C:R[-12]C)"
intTotal = intTotal + ActiveCell.Value

'move down
ActiveCell.Offset(33, 0).Select
'this is subtotal number two - the string contains 'SUBTOTAL'
ActiveCell.FormulaR1C1 = "=Sum(R[-1]C:R[-31]C)"
intTotal = intTotal + ActiveCell.Value

ActiveCell.Offset(8, 0).Select
'this is subtotal number three - the string contains 'SUBTOTAL'
ActiveCell.FormulaR1C1 = "=Sum(R[-1]C:R[-7]C)"
intTotal = intTotal + ActiveCell.Value
ActiveCell.Offset(2, 0).Select

'this is the main total
ActiveCell.Value = intTotal

Range("E9").Activate
ActiveCell.Offset(0, n).Select
move across cols to correct month (n)

For j = 1 To 66
intTotal = ActiveCell.Value
ActiveCell.Value = intTotal
ActiveCell.Offset(1, 0).Select
Next

************************

what i want to do is!
move to E9 (first cell) add up the cells in the cols in the right
month, until you hit a subtotal. there is a blank line under each
subtotal.

Then hold the subtotal value then put it into the main total....

having blonde day

thanks
Pennie
http://www.buffyslay.co.uk