Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum after every blank cell
Hello,
I love this website!! I have a problem that I am going nuts over. In column A I have several numbers from A1 to A100 In that column there may be random blank spaces like A10 is blank, A15 is blank then A40 is blank.I would like a formula in column B that sums A1to A9 then sums A11 to A14. Inotherwords, is there a formula that will give me a sum after there is a blank cell? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum after every blank cell
On 28 Jan., 00:18, wrote:
Hello, I love this website!! I have a problem that I am going nuts over. In column A I have several numbers from A1 to A100 In that column there may be random blank spaces like A10 is blank, A15 is blank then A40 is blank.I would like a formula in column B that sums A1to A9 then sums A11 to A14. Inotherwords, is there a formula that will give me a sum after there is a blank cell? Thanks Hi Try this macro. Sub AddSumIfBlank() Range("B1:B101").ClearContents tCell = "A1" tCell1 = "A1" For c = 0 To 100 If Range(tCell).Offset(c, 0) = "" Then tCell2 = Range(tCell).Offset(c, 0).Address Range(tCell2).Offset(0, 1).Formula = "=SUM(" & tCell1 & ":" & tCell2 & ")" tCell1 = tCell2 End If Next End Sub Regards, Per |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum after every blank cell
The macro works PERFECT. Can this only be achieved by macro or is
there a formula that can create the same results? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum after every blank cell
On 28 Jan., 04:32, wrote:
The macro works PERFECT. Can this only be achieved by macro or is there a formula that can create the same results? Sorry, but this can only be achieved by macro:-( If you want the macro to run every time a number is entered or deleted from the target range, try to put this in the macro sheet related to the sheet containing the data. Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Intersect(Range("A1:A100"), Target) If Not isect Is Nothing Then Range("B1:B101").ClearContents tCell = "A1" tCell1 = "A1" For c = 0 To 100 If Range(tCell).Offset(c, 0) = "" Then tCell2 = Range(tCell).Offset(c, 0).Address Range(tCell2).Offset(0, 1).Formula = "=SUM(" & tCell1 & ":" & tCell2 & ")" tCell1 = tCell2 End If Next End If End Sub Regards, Per |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum after every blank cell
Thanks, the second macro was exactly what I was looking for!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... | Excel Programming | |||
Need macro to check if cell is not blank & previous cell is blank, copy information from row above & paste | Excel Programming | |||
Copy to first Blank cell in Colum C Non blank cells still exist be | Excel Programming | |||
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) |