Autosum until previous cell with sum

[vaffer]

Hi everyone !

I have a list of items that is seperated into sections, simply by blank cells.
In every section there are items in column B and corresponding values in colulmn C.
What I want is an automatic sum of every section/day, this is exactly what the AutoSum button does, but it should happen automatically when "total" is written in the A/last row of a section.

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]day 1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]item 1[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]item 3[/TD]
[TD]320[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]item 6[/TD]
[TD]220[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]total[/TD]
[TD][/TD]
[TD]690[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]day 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]item 2[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]item 4[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]item 5[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]total[/TD]
[TD][/TD]
[TD]530[/TD]
[/TR]
</tbody>[/TABLE]

So when I type total in A11 it should sum the values of day 2 or values until the last "total" (c6:c10), and so on.

 

[skorpionkz]

try this code. Paste it to the Sheet Module where you want it to work.

Private Sub Worksheet_Change(ByVal Target As Range)
    
Dim lStart As Long, lEnd As Long
Dim irow1 As Integer, irow2 As Integer

If Target.Count > 1 Then Exit Sub

If Not Target.Column = 1 Then Exit Sub

If LCase(Target.Value) Like "total" Then
    lEnd = Target.Row - 1
    If Cells(lEnd - 1, 2) = Empty Then
        lStart = lEnd
    Else
        lStart = Cells(lEnd, 2).End(xlUp).Row
    End If
End If

irow1 = -(Target.Row - lStart)
irow2 = -1

Cells(Target.Row, 3).FormulaR1C1 = "=SUM(R[" & irow1 & "]C:R[" & irow2 & "]C)"

End Sub

 

[vaffer]

Helo skorpionkz !

Thank you, but it is not perfect.
This was only an example. I hoped to modify the code to my needs but I can't, and I have 3 problems.

1, AutoSum happens every time I type someting in column A. For example when I type "day 3" in A13, it sums A12:A1048576 with the result 0.

2, The word "total" is put in column A with an IF function, but this way the code isn't working, it sums A(ROW"total")-1:A1048576 with the result 0.

3, The autosum should happen in column G

 

[skorpionkz]

corrected code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
Dim lStart As Long, lEnd As Long
Dim irow1 As Integer, irow2 As Integer

If Target.Count > 1 Then Exit Sub
If Not Target.Column = 1 Or _
Not LCase(Target.Value) Like "total" Then Exit Sub
    
lEnd = Target.Row - 1
If Cells(lEnd - 1, 2) = Empty Then
    lStart = lEnd
Else
    lStart = Cells(lEnd, 2).End(xlUp).Row
End If

irow1 = -(Target.Row - lStart)
irow2 = -1

Cells(Target.Row, 7).FormulaR1C1 = "=SUM(R[" & irow1 & "]C:R[" & irow2 & "]C)"

End Sub