带有 VBA 中的日期条件的求和

Question

我正在努力使用以下代码进行 Sumifs。

Sub Mandays()
Application.ScreenUpdating = False
Dim Summ1 As Double, Summ2 As Double, Summ3 As Double, Summ4 As Double, 
Summ5 As Double
Dim WS1, WS2 As Worksheet
Dim Arr1 As Variant
Dim SumRng1 As Range, SumRng2 As Range, SumRng3 As Range, SumRng4 As Range, 
SumRng5 As Range
Dim CndRng1 As Range, CndRng2 As Range, CndRng3 As Range, CndRng4 As Range, 
CndRng5 As Range, CndRng6 As Range, CndRng7 As Range
Dim tFilter1 As String, tFilter2 As String, tFilter3 As String, tFilter5 As 
String
Dim i As Long, Fstdate As Long, LstDate As Long, X As Long

Set WS1 = Sheets("Daily_Production_Stats")
Set WS2 = Sheets("Master_WDD")
Arr1 = WS1.UsedRange.Value
Set SumRng1 = WS2.Range("P2:P30000")
Set SumRng2 = WS2.Range("W2:W30000")
Set SumRng3 = WS2.Range("AD2:AD30000")
Set SumRng4 = WS2.Range("AK2:AK30000")
Set SumRng5 = WS2.Range("AR2:AR30000")
Set CndRng1 = WS2.Range("J2:J30000")
Set CndRng2 = WS2.Range("Q2:Q30000")
Set CndRng3 = WS2.Range("X2:X30000")
Set CndRng4 = WS2.Range("AE2:AE30000")
Set CndRng5 = WS2.Range("AL2:AL30000")

Set CndRng6 = WS2.Range("D2:D30000")
Set CndRng7 = WS2.Range("F2:F30000")
tFilter1 = WS1.Range("D1")
Fstdate = WS1.Cells(2, 4)
X = WS1.Cells(2, Columns.Count).End(xlToLeft).Column
LstDate = WS1.Cells(2, X).Value

With WS1
.Range("A5:A300").Clear
If CndRng6 >= Fstdate And CndRng6) <= LstDate Then
For i = 3 To UBound(Arr1)
tFilter2 = WS1.Cells(i, 3)
Summ1 = .Application.WorksheetFunction.SumIfs(SumRng1, CndRng1, tFilter1, 
CndRng7, tFilter2)
Summ2 = .Application.WorksheetFunction.SumIfs(SumRng2, CndRng2, tFilter1, 
CndRng7, tFilter2)
Summ3 = .Application.WorksheetFunction.SumIfs(SumRng3, CndRng3, tFilter1, 
CndRng7, tFilter2)
Summ4 = .Application.WorksheetFunction.SumIfs(SumRng4, CndRng4, tFilter1, 
CndRng7, tFilter2)
Summ5 = .Application.WorksheetFunction.SumIfs(SumRng5, CndRng1, tFilter1, 
CndRng7, tFilter2)
.Cells(i, 1).Value = Summ1 / 480
Summ1 = 0
Next i
End If
.Activate
End With
End Sub

而If CndRng6 >= Fstdate And CndRng6) <= LstDate的台词给了我一个Type-Mismatch Error.它是比较日期。

有人可以帮助我吗？ 我也尝试过使用CLng和CDate，但没有任何效果。

Answer 1

正如用户"GMalc"在评论中提到的，您无法将整个范围与单个值进行比较。您需要找到范围内的第一个和最后一个值。然后将它们与FstDate和LstDate进行比较。

尝试使用下面的代码行而不是您拥有的代码行。

'worksheetfunction.min will find the smallest value in the range which should be the first date
'worksheetfunction.max will find the largest value in the range which should be the last date
If WorksheetFunction.Min(CndRng6) >= Fstdate And WorksheetFunction.Max(CndRng6) <= LstDate Then

希望这有帮助