Sum cells

[maytwo]

I need to sum the rows in Cell B that is blank the rows below it before the next empty cell and need to do in a loop. The City, State are the blank cells.

A B

Charleston, SC
Hamilton,V 1
Northeast, Charlotte NC
Smith,F 1
Northeast, Huntsville AL
Lenyard,J 1
Varner,P 1
South, Alpharetta GA
Proctor,J 1
South, Greenville SC
Allen,K 1
Smith,P 1
Canada,L 1

 

[mscallisto]

way can't you simply use the auto sum feature ( ? )?

the answer will be 8, is that what you're looking for?

 

[soccercook44]

So for every blank cell you need to sum the values below it, or until the next blank cell?

 

[maytwo]

Soccercook44 - Yes

 

[Bong]

sumC=0
for each c in sheets(1).columns(2).cells
 sumC=sumC+c.value
 if c.value=0 then
    c.Offset(rowOffset:=0, columnOffset:=1).value = sumC
    sumC=0
 end if
next

_________________
Bob Rashkin

 

[MajP]

The following code populates the sheet with this.
[tt]
TotalForCity
Charleston,SC 1
Hamilton,V 1
Northeast,Charlotte NC 1
Smith,F 1
Northeast,Huntsville AL 4
Lenyard,J 1
Varner,P 1
South,Alpharetta GA 1
Proctor,J 1
South,Greenville SC 2
Allen,K 1
Smith,P 1
Canada,L
[/tt]

Public Sub filldata()
  Dim rng As Range
  Dim ws As Worksheet
  Set ws = ActiveWorkbook.ActiveSheet
  Set rng = ws.Range("B1")
  Do Until Trim(rng.Value & " ") = "" And Trim(rng.Offset(1, 0).Value & " ") = ""
    rng.Offset(0, 1) = SumContigous(rng)
    Set rng = getNextEmptyCell(rng)
  Loop
End Sub
Public Function SumContigous(rng As Range) As Variant
  Dim i As Integer
  i = 1
  Do Until Trim(rng.Offset(i, 0) & " ") = ""
    SumContigous = SumContigous + rng.Offset(i, 0).Value
    i = i + 1
  Loop
End Function

Public Function getNextEmptyCell(rng As Range) As Range
  Dim i As Integer
  i = 1
  Do Until Trim(rng.Offset(i, 0) & " ") = ""
    i = i + 1
  Loop
  Set getNextEmptyCell = rng.Offset(i, 0)
End Function

 

[MajP]

Formatted:

                             TotalForCity  
Charleston,SC                      1 
Hamilton,V                 1	
Northeast,Charlotte NC             1
Smith,F                    1	
Northeast,Huntsville AL            4
Lenyard,J                  1	
Varner,P                   1	
South,Alpharetta GA        1	
Proctor,J                  1	
South,Greenville SC                2
Allen,K                    1	
Smith,P                    1	
Canada,L

 

[maytwo]

Thank you - that works, but do not need to put in Column C
- can we change to place total in Column B? Works great...

 

[MajP]

rng.Offset(0, 1) = SumContigous(rng)
to
rng = sumContigous(rng)

 

[maytwo]

I fixed it ....


Thank you