Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help (Uppercase multiple ranges?) | Excel Discussion (Misc queries) | |||
Have a formula yield a true blank that disconnects graph line | Charts and Charting in Excel | |||
Macro to hide blank cells in a range | Excel Discussion (Misc queries) | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
enter numbers in blank cells | Excel Worksheet Functions |