VBA returning 1004: Range of Object Global failed

SumIf in VBA

• Here's a few ways how you can do it.

Option Explicit

Sub CompareRange()
    
    Dim rSum As Long
    Dim i As Long
    
    For i = 4 To 205
        If Range("G" & i).Value = #8/22/2020# Then
            rSum = rSum + Range("D" & i).Value
        End If
    Next i
    
    Cells("G206").Value = rSum

End Sub

Sub CompareCells()
    
    Dim rSum As Long
    Dim i As Long
    
    For i = 4 To 205
        If Cells(i, "G").Value = #8/22/2020# Then
            rSum = rSum + Cells(i, "D").Value
        End If
    Next i
    
    Cells(206, "G").Value = rSum

End Sub

Sub CompareLastRow()
   
    Const fRow As Long = 4
    
    Dim lRow As Long: lRow = Cells(Rows.Count, "G").End(xlUp).Row
    
    Dim rSum As Long
    Dim i As Long
    
    For i = fRow To lRow
        If Cells(i, "G").Value = #8/22/2020# Then
            rSum = rSum + Cells(i, "D").Value
        End If
    Next i
    
    Cells(lRow + 1, "G").Value = rSum

End Sub

Sub CompareObjects()
   
    Const fRow As Long = 4
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row
    
    Dim rSum As Long
    Dim i As Long
    
    For i = fRow To lRow
        If ws.Cells(i, "G").Value = #8/22/2020# Then
            rSum = rSum + ws.Cells(i, "D").Value
        End If
    Next i
    
    ws.Cells(lRow + 1, "G").Value = rSum

End Sub

Sub CompareObjectsRange()
   
    Const fRow As Long = 4
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row
    Dim rg As Range: Set rg = ws.Range(ws.Cells(fRow, "G"), ws.Cells(lRow, "G"))
    
    ws.Cells(lRow + 1, "G").Value _
        = Application.SumIf(rg, #8/22/2020#, rg.Offset(, -3))
    
End Sub