excel - VBA 返回 1004 : Range of Object Global failed

Question

Sub Compare()
    Dim sum As Long
    Dim i As Long
    For i = 4 To 205
        If Range(i, "G").Value = "08/22/202" Then
            sum = sum + Range("D", i).Value
            If i = 205 Then
                Exit For
            End If
        End If
    Next i
    Range("G", 206).Value = sum

End Sub

Answer 1

VBA 中的 SumIf

这里有几种方法可以做到这一点。

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