sum of variable range stop working

[YOYO]

I have a code for formatting some data and need subtotal some cells based on the description, like " Deposits", " Checks" etc. recently, sum doesnt work any more.
Please help.

please see code below:

Sub FormatDeposits()


Sheets("Deposits and Additions").Select


Rows("1:1").Delete Shift:=xlUp
Call DeleteRows

Range("F1") = "=RC[-4]"
Range("G1") = "=LEFT(RC[-1],7)"
Range("F1").AutoFill Destination:=Range("F1:F" & Cells(Rows.Count, "E").End(xlUp).Row)
Range("G1").AutoFill Destination:=Range("G1:G" & Cells(Rows.Count, "E").End(xlUp).Row)

Columns("F:G").Select
Range("F3").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Columns("F:F").Select
Range("F1").Activate
Selection.Replace What:=" *", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=" for*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Columns("A:G").Select
Range("G1").Activate
Application.AddCustomList ListArray:=Array("Category", "DEPOSIT", "ORIG CO", "LOCKBOX", "REVERSA")
ActiveWorkbook.Worksheets("Deposits and Additions").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Deposits and Additions").Sort.SortFields.Add Key:= _
Range("G1:G1902"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder _
:="Category,DEPOSIT,ORIG CO,LOCKBOX,REVERSA", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Deposits and Additions").Sort
.SetRange Range("A1:G1902")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


Columns("B:B").Insert Shift:=xlToRight

Call AddBlankRows
Call AddBlankRows2

Columns("C:C").Select
Range("C1").Activate
Selection.Replace What:=" *", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=" for*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

'Sum of above cells
lastrow = Cells(Rows.Count, 4).End(xlUp).Row
Cells(lastrow + 1, 4).Formula = "=SUMIFS(C,C[4],R[-1]C[4])"
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
With Range("D" & lastrow)
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = xlDouble
.Font.Bold = True
End With

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(lastrow + 1, 1).Formula = "Subtotal"
With Range("A" & lastrow + 1)
.Font.Bold = True
End With

lastrow = Cells(Rows.Count, 3).End(xlUp).Row
Cells(lastrow + 1, 3).Formula = "=R[-1]C"
With Range("C" & lastrow + 1)
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With

Columns("D:D").Select
Range("D1").Activate
Selection.Style = "Comma"
Columns("A:D").Columns.AutoFit

Range("A1:H999999").Activate
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:H").EntireColumn.Delete

Rows("1:1").EntireRow.Insert
Range("A1") = "Date"
Range("B1") = "Your Ref Number"
Range("C1") = "Description"
Range("D1") = "Amount"
Range("A1:D1").Font.Underline = xlUnderlineStyleSingleAccounting
Rows("1:1").Font.Bold = True

End Sub

[mdmackillop]

Please post a workbook with sample data Go Advanced / Manage Attachments

[YOYO]

I run some test with the code and i realized it is the data range, so i changed the range in code and now it works.
Thank you.