VBA-Sumif across ranges

[Miya]

Hi i am after a VBA code that will apply the below formula to very last Total. So the code looks for Total from bottom to Top, finds the first Total, applies the below formula and then fills across to Col S22

=SUMIF($A$2:$A$20,"TOTAL",B2:B20).

This data is cleared everyday therefore i want VBA to input formula

Excel Workbook
	A	B
2	Team	No.of items
3	TEST1	5
4	TEST2	44
5	TEST3	1
6	TEST4	0
7	TEST5	5
8	TOTAL	477
9
10	TEST6	0
11	TOTAL	644
12
13	TEST7	0
14	TOTAL	0
15
16	TEST8	0
17	TOTAL	0
18
19	TEST9	0
20	TOTAL	0
21
22	TOTAL	1121
Sheet2

 

[rudfaden]

Try this:

Sub Autofill()
Dim rCell As Range
Dim rRange As Range
Set rRange = Range("A1:A21")

For Each rCell In rRange
    If rCell = "TOTAL" Then
      rCell.Offset(0, 1) = "=SUMIF($A$2:$A$20,TOTAL,B2:B20)"
     rCell.Offset(0, 1).Autofill Destination:=Range(Cells(rCell.Row, 2), Cells(rCell.Row, 22))
    End If
Next
End Sub

 

[wigi]

I would go for:

Sub Autofill_Wigi()
    Range("A" & Rows.Count).End(xlUp).Offset(, 2).Resize(, 17).Formula = "=SUMIF($A$2:$A$20,TOTAL,B2:B20)"
End Sub

 

[Miya]

I would go for:

Sub Autofill_Wigi()
    Range("A" & Rows.Count).End(xlUp).Offset(, 2).Resize(, 17).Formula = "=SUMIF($A$2:$A$20,TOTAL,B2:B20)"
End Sub

Thanks that works but i am bit worried if the user either inserts a row or deletes a row then the code would not adjust to the new ranges since the ranges are hard-coded, is there a way around this?

 

[wigi]

Then...

Sub Autofill_Wigi()
    With Range("A" & Rows.Count).End(xlUp)
        .Offset(, 2).Resize(, 17).Formula = "=SUMIF($A$2:$A$" & .Row - 1 & ",TOTAL,B2:B" & .Row - 1 & ")"
    End With
End Sub

Wigi

 

[Miya]

Perfect, thanks

Then...

Sub Autofill_Wigi()
    With Range("A" & Rows.Count).End(xlUp)
        .Offset(, 2).Resize(, 17).Formula = "=SUMIF($A$2:$A$" & .Row - 1 & ",TOTAL,B2:B" & .Row - 1 & ")"
    End With
End Sub

Wigi