Sub 日期别()
On Error Resume Nextlastrow = Sheets("运营日报").Range("a1048576").End(xlUp).Rowlastday = VBA.Day(Sheets("运营日报").Range("a" & lastrow)) + 1lastcolumn = Sheets("运营统计表").Range("ag3").End(xlToLeft).Column + 1For daterow = lastcolumn To lastdayWith Sheets("运营统计表")Set d = CreateObject("scripting.dictionary")Set d1 = CreateObject("scripting.dictionary")Set d2 = CreateObject("scripting.dictionary")Set d3 = CreateObject("scripting.dictionary")Set d4 = CreateObject("scripting.dictionary")arr = Sheets("运营日报").Range("a1:y" & lastrow)For a = 4 To UBound(arr)If VBA.Day(arr(a, 1)) = .Cells(2, daterow) Then If Not d.exists(arr(a, 3)) Then '订单号的去重计数 d.Add arr(a, 3), "" End If If Not d1.exists(arr(a, 4)) Then '商户的去重计数 d1.Add arr(a, 4), "" End If If Not d2.exists(arr(a, 5)) Then 'B端门店数的去重计数 d2.Add arr(a, 5), "" End IfIf Sheets("运营日报").Range("o" & a) = "收空瓶" Then
If Not d3.exists(arr(a, 5)) Then '特殊门店去重计数 d3.Add arr(a, 5), "" End If d4(arr(a, 1)) = d4(arr(a, 1)) + arr(a, 8) '特殊业务件数的汇总求和 End IfEnd IfNextcou = d.Countcou1 = d1.Countcou2 = d2.Countcou3 = d3.Countcou4 = d4.items.Cells(3, daterow) = cou.Cells(4, daterow) = cou1.Cells(5, daterow) = cou2.Cells(9, daterow) = cou3.Cells(11, daterow) = cou4d.RemoveAlld1.RemoveAlld2.RemoveAlld3.RemoveAlld4.RemoveAll'--------------
With Sheets("运营日报")dat = DateSerial(2018, VBA.Month(Sheets("运营日报").Range("a" & lastrow)), daterow - 1)'vba.year,month,day,这些属性虽然要求填写date类型的值,但是填入文本型也是可以的。
'dateserial 属性可以是integer,这个长整型,可以是时间格式,可以是文本格式的数字,可以是数字格式的。
Sheets("运营统计表").Cells(6, daterow) = WorksheetFunction.SumIfs(.Range("h4:h" & lastrow), .Range("a4:a" & lastrow), _dat)Sheets("运营统计表").Cells(7, daterow) = WorksheetFunction.SumIfs(.Range("i4:i" & lastrow), .Range("a4:a" & lastrow), _dat) / 1000Sheets("运营统计表").Cells(8, daterow) = WorksheetFunction.SumIfs(.Range("j4:j" & lastrow), .Range("a4:a" & lastrow), _dat)Sheets("运营统计表").Cells(10, daterow) = Sheets("运营统计表").Cells(9, daterow) / Sheets("运营统计表").Cells(5, daterow)Sheets("运营统计表").Cells(12, daterow) = Sheets("运营统计表").Cells(11, daterow) / Sheets("运营统计表").Cells(6, daterow)Sheets("运营统计表").Cells(13, daterow) = WorksheetFunction.CountIfs(.Range("v4:v" & lastrow), "*异常", .Range("a4:a" & lastrow), _dat)Sheets("运营统计表").Cells(14, daterow) = Sheets("运营统计表").Cells(13, daterow) / Sheets("运营统计表").Cells(3, daterow)Sheets("运营统计表").Cells(15, daterow) = WorksheetFunction.CountIfs(.Range("v4:v" & lastrow), "<>*订单超时异常*", .Range("a4:a" & lastrow), _dat)Sheets("运营统计表").Cells(16, daterow) = Sheets("运营统计表").Cells(15, daterow) / Sheets("运营统计表").Cells(3, daterow) End With'-------------
End WithNextEnd Sub