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.

	A	B	C
1	day 1
2		item 1	150
3		item 3	320
4		item 6	220
5	total		690
6
7	day 2
8		item 2	180
9		item 4	250
10		item 5	100
11	total		530

<tbody>
</tbody>

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