[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Macro that sums cells in rows of varible number and....

blazzzercat

12/19/2006 9:40:00 PM

I need a macro that will SUM the cells in rows that vary in the number of
rows and put the total in the column next to the last number before a blank
row. It will then go down the column until it finds another row with a number
in it and SUM those rows until the next blank row, loop until no more rows
with numbers are found. The cells always start in I-8. I need the totals in
column J.

Thanks so much in advance.
3 Answers

Charles Chickering

12/19/2006 9:52:00 PM

0

Sub SumStuff()
Dim rLastCell As Range
Dim rFirstSum As Range
Dim rLastSum As Range
Set rLastCell = Range("I" & Rows.Count).End(xlUp)
Set rFirstSum = Range("I8")
Do
If rFirstSum.Offset(1) = "" Then
rFirstSum.Offset(, 1).Formula = _
"=Sum(" & rFirstSum.Address & ")"
Else
Set rLastSum = rFirstSum.End(xlDown)
rLastSum.Offset(, 1).Formula = _
"=Sum(" & Range(rFirstSum, rLastSum).Address & ")"
End If
Loop Until rFirstSum.Row >= rLastCell.Row Or _
rLastSum.Row >= rLastCell.Row
End Sub

Try that.
--
Charles Chickering

"A good example is twice the value of good advice."


"blazzzercat" wrote:

> I need a macro that will SUM the cells in rows that vary in the number of
> rows and put the total in the column next to the last number before a blank
> row. It will then go down the column until it finds another row with a number
> in it and SUM those rows until the next blank row, loop until no more rows
> with numbers are found. The cells always start in I-8. I need the totals in
> column J.
>
> Thanks so much in advance.
>
>

Charles Chickering

12/19/2006 10:16:00 PM

0

Oops forgot to move rFirstSum to the next group of numbers.
Sub SumStuff()
Dim rLastCell As Range
Dim rFirstSum As Range
Dim rLastSum As Range
Set rLastCell = Range("I" & Rows.Count).End(xlUp)
Set rFirstSum = Range("I8")
Do
If rFirstSum.Offset(1) = "" Then
rFirstSum.Offset(, 1).Formula = _
"=Sum(" & rFirstSum.Address & ")"
Set rFirstSum = rFirstSum.End(xlDown)
Else
Set rLastSum = rFirstSum.End(xlDown)
rLastSum.Offset(, 1).Formula = _
"=Sum(" & Range(rFirstSum, rLastSum).Address & ")"
Set rFirstSum = rLastSum.End(xlDown)
End If
Loop Until rFirstSum.Row >= rLastCell.Row Or _
rLastSum.Row >= rLastCell.Row
End Sub


Sry 'bout that.
--
Charles Chickering

"A good example is twice the value of good advice."


"Charles Chickering" wrote:

> Sub SumStuff()
> Dim rLastCell As Range
> Dim rFirstSum As Range
> Dim rLastSum As Range
> Set rLastCell = Range("I" & Rows.Count).End(xlUp)
> Set rFirstSum = Range("I8")
> Do
> If rFirstSum.Offset(1) = "" Then
> rFirstSum.Offset(, 1).Formula = _
> "=Sum(" & rFirstSum.Address & ")"
> Else
> Set rLastSum = rFirstSum.End(xlDown)
> rLastSum.Offset(, 1).Formula = _
> "=Sum(" & Range(rFirstSum, rLastSum).Address & ")"
> End If
> Loop Until rFirstSum.Row >= rLastCell.Row Or _
> rLastSum.Row >= rLastCell.Row
> End Sub
>
> Try that.
> --
> Charles Chickering
>
> "A good example is twice the value of good advice."
>
>
> "blazzzercat" wrote:
>
> > I need a macro that will SUM the cells in rows that vary in the number of
> > rows and put the total in the column next to the last number before a blank
> > row. It will then go down the column until it finds another row with a number
> > in it and SUM those rows until the next blank row, loop until no more rows
> > with numbers are found. The cells always start in I-8. I need the totals in
> > column J.
> >
> > Thanks so much in advance.
> >
> >

Jef Gorbach

12/20/2006 7:07:00 AM

0

give this a shot

Sub test()
'place a subtotal in column(J) wherever column(I) has a blank cell
lrow = Range("i65536").End(xlUp).Row + 1
For Each cell In Range("i8:i" & lrow)
If cell.Value = isblank Then
cell.Offset(0, 1).Formula = "=SUM(R[" & -rowcount &
"]C[-1]:R[-1]C[-1])"
rowcount = 0
Else
rowcount = rowcount + 1
End If
Next
End Sub


"blazzzercat" <u30352@uwe> wrote in message news:6b0307dd832cf@uwe...
> I need a macro that will SUM the cells in rows that vary in the number of
> rows and put the total in the column next to the last number before a
blank
> row. It will then go down the column until it finds another row with a
number
> in it and SUM those rows until the next blank row, loop until no more rows
> with numbers are found. The cells always start in I-8. I need the totals
in
> column J.
>
> Thanks so much in advance.
>