|
Sub 计算() Dim ArrYS, ArrJG, ArrZZ Dim i&, j&, Temp& Dim d As Object Dim SDate, EDate ArrYS = Sheet1.Range("F3:AH" & Sheet1.Range("F65536").End(xlUp).Row) ArrZZ = Sheet3.Range("A2:C" & Sheet3.Range("A65536").End(xlUp).Row) Set d = CreateObject("Scripting.Dictionary") With Sheet2 SDate = .Range("m1") EDate = .Range("o1") For k = 1 To 2 ArrJG = .Range(.Cells(4, k * 6 - 5), .Cells(.Cells(.Cells.Rows.Count, k * 6 - 5).End(xlUp).Row, k * 6 - 1)) d.RemoveAll For i = 1 To UBound(ArrJG) d(ArrJG(i, 1)) = i ArrJG(i, 3) = WorksheetFunction.VLookup(ArrJG(i, 1), ArrZZ, 3, 0) ArrJG(i, 4) = 0 ArrJG(i, 5) = 0 Next i For i = 1 To UBound(ArrYS) If d.exists(ArrYS(i, 29)) Then Temp = d(ArrYS(i, 29)) If ArrYS(i, 1) < SDate Then ArrJG(Temp, 3) = ArrJG(Temp, 3) + ArrYS(i, 14) - ArrYS(i, 15) ElseIf ArrYS(i, 1) >= SDate And ArrYS(i, 1) <= EDate Then ArrJG(Temp, 4) = ArrJG(Temp, 4) + ArrYS(i, 14) ArrJG(Temp, 5) = ArrJG(Temp, 5) + ArrYS(i, 15) End If End If Next i .Cells(4, k * 6 - 5).Resize(UBound(ArrJG, 1), UBound(ArrJG, 2)) = ArrJG Next k End With End Sub |
|