Never mind -- got it, those column values in the SUM() expresseions should
have been -6 and -7. All good and wonderful!

Thanks a million.
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Bernie Deitrick" wrote:

Dave,

I thought that since your macro was using SUM you wanted sums....

Anyway, this macro assumes that your years are in column A, and you want your formulas in columns H,
J, and L. (But you never described your actual layout.) It will do all the data at one time.

Sub TryNow()
Dim myCount As Integer
Dim myCell As Range
myCount = 0
For Each myCell In Range("A2", Range("A65536").End(xlUp))
If myCell.Value < myCell(2, 1).Value Then
myCell(1, 8).FormulaR1C1 = "=RC[-6]"
myCell(1, 10).FormulaR1C1 = "=SUM(R[-" & myCount & "]C[-6]:RC[-6])"
myCell(1, 12).FormulaR1C1 = "=SUM(R[-" & myCount & "]C[-7]:RC[-7])"
myCount = 0
Else
myCount = myCount + 1
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP


"Dave Birley" wrote in message
...
Sorry, but I'm a VFP and xBase programmer from way back (that's why I know
what I would like <g) but this is the first time in my life I have done
anything that looks like programming in Excel. I like the idea of "At each
change in...", but when I entered the configuration dialog, I thought I could
use Count -- pass the results to a new column created to receive them. Didn't
work. Thank goodness for Ctrl-Z <G!
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Bernie Deitrick" wrote:

Dave,

Try using the subtotal feature of Excel. Select your table, then use Data / Subtotals.... and
pick
your coulmn with year as the "At each change in.." column, and the sum your columns as desired.

And if there are other columns of identifiers, consider using a Pivot table...

HTH,
Bernie
MS Excel MVP


"Dave Birley" wrote in message
...
Here is a simple macro I am using:

Sub Rows4()
'
' Rows4 Macro
' Macro recorded 4/20/2007 by Administrator
'

'
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=RC[-6]"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C[-6]:RC[-6])"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C[-7]:RC[-7])"
ActiveCell.Offset(1, -4).Range("A1").Select
End Sub

It is one of a family of 11 macros, each named "Rows[n]", and all I have to
do is count how many rows I need to apply a macro to, and call the macro
named for that number of rows with its hotkey. However, being fundamentally
lazy (always give the hardest task to the laziest employee, and he/she will
always find the easiest way to do it <g) -- I want to extend it a tiny bit.

I would like to change that last line to:

ActiveCell.Offset(1, -10).Range("A1").Select

This column contains years, sequentially sorted. So there could be four 1996
then five 1997, then eleven 1998 then two 1996 again. What I need to do,
after making that select is this:

--Capture the value in the selected cell
--Scroll down, counting the rows until I find a different value
--Scroll back up one row, and hold the adjusted count for number of rows
--Scroll back to the right 6 columns to my "starting cell"
--Invoke the Macro based on the number of rows counted ("Rows3" e.g.)

The table has nearly 9,000 rows, and doing this junk manually I have reached
row 800 in some two hours. If I could modify the macros to do this, it would
do the whole shebang in a couple of heartbeats!

All contributions gratefully received.
Dave
Temping with Staffmark
in Rock Hill, SC