和Excel函数date同样功能的VBA函数DateSerial用法

Sub 日期别()
On Error Resume Next
lastrow = Sheets("运营日报").Range("a1048576").End(xlUp).Row
lastday = VBA.Day(Sheets("运营日报").Range("a" & lastrow)) + 1
lastcolumn = Sheets("运营统计表").Range("ag3").End(xlToLeft).Column + 1
For daterow = lastcolumn To lastday
With 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 If

If 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 If
End If
Next
cou = d.Count
cou1 = d1.Count
cou2 = d2.Count
cou3 = d3.Count
cou4 = d4.items
.Cells(3, daterow) = cou
.Cells(4, daterow) = cou1
.Cells(5, daterow) = cou2
.Cells(9, daterow) = cou3
.Cells(11, daterow) = cou4
d.RemoveAll
d1.RemoveAll
d2.RemoveAll
d3.RemoveAll
d4.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) / 1000
Sheets("运营统计表").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 With
Next
End Sub

 

转载于:https://www.cnblogs.com/shida-liu/p/9343089.html