|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub AwDemo2()
Dim i&, j%, n%, r&, k&, m%, s%, x%, y%, ym1$, ym2$, iStr$
Dim arr, kAr, tAr, begDay$, endDay$, d As Object
Application.ScreenUpdating = False
Set d = CreateObject("Scripting.Dictionary")
begDay = InputBox("请输入开始日期,格式如:202001")
If StrPtr(begDay) = 0 Then Exit Sub
endDay = InputBox("请输入结束日期,格式如:202001")
If StrPtr(endDay) = 0 Then Exit Sub
With Sheets("数据")
arr = .[a1].CurrentRegion
For i = 2 To UBound(arr)
If arr(i, 4) = "停发" Then s = -1 Else s = 1
iStr = arr(i, 1) & "|" & arr(i, 2)
d(iStr & "|" & "Num") = d(iStr & "|" & "Num") + 1
d(iStr & "|" & "Sum") = d(iStr & "|" & "Sum") + s
d(iStr) = d(iStr & "|" & "Num") & "|" & d(iStr & "|" & "Sum")
Next
kAr = d.keys: tAr = d.items
ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2) + 2)
For j = 1 To UBound(arr, 2)
brr(1, j) = arr(1, j)
Next
brr(1, UBound(brr, 2) - 1) = "停发次数"
brr(1, UBound(brr, 2)) = "续发次数"
r = 1
For i = 2 To UBound(arr)
If arr(i, 3) >= begDay And arr(i, 3) <= endDay Then
iStr = arr(i, 1) & "|" & arr(i, 2)
x = Split(d(iStr), "|")(0): y = Split(d(iStr), "|")(1)
If arr(i, 4) = "停发" Then n = UBound(brr, 2) - 1 Else n = UBound(brr, 2)
If x <> Abs(y) Then
If Not d.exists(iStr & "|" & "mark") Then
ym1 = "": ym2 = ""
r = r + 1
d(iStr & "|" & "mark") = r
For j = 1 To UBound(arr, 2)
brr(r, j) = arr(i, j)
Next
d(iStr & "|" & arr(i, 4)) = 1
If n = UBound(brr, 2) - 1 Then '这句修改一下
ym1 = arr(i, 3)
brr(r, n) = 1 & "次" & "(" & ym1 & ")"
Else
ym2 = arr(i, 3)
brr(r, n) = 1 & "次" & "(" & ym2 & ")"
End If
Else
k = d(iStr & "|" & "mark")
For j = 1 To UBound(arr, 2)
brr(k, j) = arr(i, j)
Next
d(iStr & "|" & arr(i, 4)) = d(iStr & "|" & arr(i, 4)) + 1
m = d(iStr & "|" & arr(i, 4))
If n = UBound(brr, 2) - 1 Then '这句修改一下
ym1 = ym1 & "," & arr(i, 3)
brr(k, n) = m & "次" & "(" & ym1 & ")"
Else
ym2 = ym2 & "," & arr(i, 3)
brr(k, n) = m & "次" & "(" & ym2 & ")"
End If
End If
End If
End If
Next
End With
With Sheets("停发续发")
.Cells.Clear
If r > 0 Then
.[a1].Resize(r, UBound(brr, 2)) = brr
End If
End With
Application.ScreenUpdating = True
End Sub |
评分
-
2
查看全部评分
-
|