|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 笨鸟飞不高 于 2020-7-19 00:39 编辑
Sub AwTest()
Dim i&, r&, j%, DayM%, iStr$, DayStr$, begDay$, endDay$, iDay As Date
Dim arr, 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, 3) >= begDay And arr(i, 3) <= endDay Then
iStr = arr(i, 1) & "|" & arr(i, 2) & "|" & arr(i, 4)
d(iStr) = ""
End If
Next
ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2) + 1)
For j = 1 To UBound(arr, 2)
brr(1, j) = arr(1, j)
Next
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) & "|" & "续发"
If Not d.exists(iStr) Then
r = r + 1
For j = 1 To UBound(arr, 2)
brr(r, j) = arr(i, j)
Next
DayStr = Left(brr(r, 3), 4) & "-" & Right(brr(r, 3), 2)
iDay = CDate(DayStr)
DayM = DateDiff("m", iDay, CDate(Left(endDay, 4) & "-" & Right(endDay, 2)))
If DayM > 0 Then brr(r, 7) = DayM \ 12 & "年" & DayM Mod 12 & "个月"
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
一直停发代码!增加开始日期判断,数据列不固定,是这意思吗? |
|