- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("需求")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- c = .Cells(2, .Columns.Count).End(xlToLeft).Column
- .Range("q3").Resize(r - 2, c - 16).ClearContents
- arr = .Range("a2").Resize(r - 1, c)
- For i = 2 To UBound(arr)
- xm = arr(i, 2) & "+" & arr(i, 3)
- d(xm) = i
- Next
- End With
- With Worksheets("sys")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- brr = .Range("a2:h" & r)
- For i = 1 To UBound(brr)
- xm = brr(i, 7) & "+" & brr(i, 3)
- If d.exists(xm) Then
- m = d(xm)
- If brr(i, 1) >= #6/26/2019# And brr(i, 1) <= #8/9/2019# Then
- n = DateDiff("d", #6/26/2019#, brr(i, 1)) * 2 + 17
- arr(m, n) = arr(m, n) + brr(i, 6)
- End If
- End If
- Next
- End With
- For i = 2 To UBound(arr)
- arr(i, 18) = arr(i, 10) - arr(i, 17)
- For j = 20 To UBound(arr, 2) Step 2
- arr(i, j) = arr(i, j - 2) - arr(i, j - 1)
- Next
- For j = 18 To UBound(arr, 2) Step 2
- If arr(i, j) < 0 Then
- Exit For
- End If
- Next
- If j <= UBound(arr, 2) Then
- arr(i, 16) = arr(1, j)
- Else
- arr(i, 16) = ""
- End If
- Next
-
- With Worksheets("需求")
- .Range("a2").Resize(UBound(arr), UBound(arr, 2)) = arr
- End With
- End Sub
复制代码 |