|
- Sub tt()
- zd = CDate([e9]) '指定时间
- arr = Sheets("数据源").[a1].CurrentRegion
- brr = Sheets("需求结果").Range("a5:c16")
- crr = arr: n = 1 '滞留批次
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- For i = 1 To UBound(brr) '站点和目标时间的关系
- d1(brr(i, 1)) = brr(i, 2)
- Next
- For i = 2 To UBound(arr) '站点和目标时间的关系
- If d1.exists(arr(i, 1)) Then '有目标时间的站点
- zz = 24 * (zd - CDate(arr(i, 3))) '在制时间
- If zz >= d1(arr(i, 1)) Then '如果在制时间大于目标时间
- x = arr(i, 1) & arr(i, 2)
- If Not d2.exists(x) Then '站点+批次去重(相同的只计一次)
- d2(x) = ""
- d(arr(i, 1)) = d(arr(i, 1)) + 1
- n = n + 1
- For j = 1 To UBound(arr, 2)
- crr(n, j) = arr(i, j)
- Next
- End If
- End If
- End If
- Next
- For i = 1 To UBound(brr) '根据站点求出超时批次数
- brr(i, 3) = d(brr(i, 1))
- Next
- Sheets("需求结果").Range("c5:c16").ClearContents
- Sheets("需求结果").Range("a5:c16") = brr
- Sheets("滞留批次").Cells.ClearContents
- Sheets("滞留批次").[a1].Resize(n, UBound(crr, 2)) = crr
- End Sub
复制代码 |
|