|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub test()
- Dim r%, i%
- Dim arr, brr, crr()
- Dim d As Object
- wd = Array(0, 1142.61, 374.43, 684.79, 508.47)
- Set d = CreateObject("scripting.dictionary")
- f1 = True
- With Worksheets("sheet1")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:i" & r)
- i = 1
- Do While i <= UBound(arr)
- If Not d.exists(arr(i, 2)) Then
- If arr(i, 7) >= 10 Then
- ReDim brr(1 To 4, 1 To 7)
- ReDim crr(6 To 9)
- brr(1, 1) = DateSerial(arr(i, 2), arr(i, 3), arr(i, 4))
- For j = 6 To 9
- crr(j) = arr(i, j)
- Next
- d(arr(i, 2)) = brr
- nf = arr(i, 2)
- t = 1
- End If
- Else
- If t <= 4 Then
- For j = 6 To 9
- crr(j) = crr(j) + arr(i, j)
- Next
- If crr(7) >= wd(t) Then
- brr(t, 2) = DateSerial(arr(i, 2), arr(i, 3), arr(i, 4))
- brr(t, 3) = DateDiff("d", brr(t, 1), brr(t, 2))
- For j = 6 To 9
- brr(t, j - 2) = crr(j)
- Next
- d(nf) = brr
- t = t + 1
- i = i + 1
- If t <= 4 Then
- brr(t, 1) = DateSerial(arr(i, 2), arr(i, 3), arr(i, 4))
- End If
- For j = 6 To 9
- crr(j) = arr(i, j)
- Next
- End If
- End If
- End If
- i = i + 1
- Loop
- .Range("j2:p" & .Rows.Count).ClearContents
- For Each aa In d.items
- r = .Cells(.Rows.Count, 10).End(xlUp).Row + 1
- .Cells(r, 10).Resize(4, UBound(aa, 2)) = aa
- Next
- End With
- End Sub
复制代码 |
|