how do I write macro to sum numbers up to next blank line?
 

I want to write a macro that adds up numbers to the next blank line.
Sometimes there might be 2 numbers to add up, sometimes 15. It seems like
there should be a way to say +sum(move left, move up, end up(for grabbing
that cell and all the ones above it to the next blank line). I can't figure
out a way to do this.

Here is an example of what I want to do (but in a macro)

1
1
1
=+SUM(A1:A3)
1
1
1
1
=+SUM(A5:A8)
1
1
1
1
1
=+SUM(A10:A14)
1
1
1
1
1
1
1
=+SUM(A16:A22)
1
1
1
1
1
1
1
1 =+SUM(A24:A30)
1
1
1
=+SUM(A32:A34)

--
Thanks Very Much!

Art Nittskoff

how do I write macro to sum numbers up to next blank line?
 

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
Dim iStart As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
iStart = 1
For i = 1 To iLastRow + 1
If .Cells(i, "A").Value = "" Then
Cells(i, "A").Value = Application.Sum(.Range(.Cells(iStart,
"A"), .Cells(i - 1, "A")))
iStart = i + 1
End If
Next i

End With

End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Art Nittskoff" wrote in message
...
I want to write a macro that adds up numbers to the next blank line.
Sometimes there might be 2 numbers to add up, sometimes 15. It seems like
there should be a way to say +sum(move left, move up, end up(for grabbing
that cell and all the ones above it to the next blank line). I can't
figure
out a way to do this.

Here is an example of what I want to do (but in a macro)

1
1
1
=+SUM(A1:A3)
1
1
1
1
=+SUM(A5:A8)
1
1
1
1
1
=+SUM(A10:A14)
1
1
1
1
1
1
1
=+SUM(A16:A22)
1
1
1
1
1
1
1
1 =+SUM(A24:A30)
1
1
1
=+SUM(A32:A34)

--
Thanks Very Much!

Art Nittskoff

how do I write macro to sum numbers up to next blank line?
 

Bob,

I appreciate the answer...but to be honest, I don't understand it. What do I
do with it? Do I cut and paste it into something? How? I also want to add
that after it is done it goes down to the next blank line as does the formula
again.

I appreciate the response, I am just not "excel literate" enough to
understand it.

Art
--
Thanks Very Much!

Art Nittskoff


"Bob Phillips" wrote:

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
Dim iStart As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
iStart = 1
For i = 1 To iLastRow + 1
If .Cells(i, "A").Value = "" Then
Cells(i, "A").Value = Application.Sum(.Range(.Cells(iStart,
"A"), .Cells(i - 1, "A")))
iStart = i + 1
End If
Next i

End With

End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Art Nittskoff" wrote in message
...
I want to write a macro that adds up numbers to the next blank line.
Sometimes there might be 2 numbers to add up, sometimes 15. It seems like
there should be a way to say +sum(move left, move up, end up(for grabbing
that cell and all the ones above it to the next blank line). I can't
figure
out a way to do this.

Here is an example of what I want to do (but in a macro)

1
1
1
=+SUM(A1:A3)
1
1
1
1
=+SUM(A5:A8)
1
1
1
1
1
=+SUM(A10:A14)
1
1
1
1
1
1
1
=+SUM(A16:A22)
1
1
1
1
1
1
1
1 =+SUM(A24:A30)
1
1
1
=+SUM(A32:A34)

--
Thanks Very Much!

Art Nittskoff