|
本帖最后由 dsmch 于 2014-4-19 15:59 编辑
- Sub Macro1()
- Dim arr, brr, crr, d, i&, j&, zf$, p$
- Set d = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("a1").CurrentRegion
- ReDim crr(1 To UBound(arr) - 1, 1 To 4)
- brr = Sheet2.Range("a1").CurrentRegion
- Range("a2:e60000").ClearContents
- For i = 2 To UBound(arr)
- zf = arr(i, 1) & "," & arr(i, 2)
- d(zf) = d(zf) & "," & i
- Next
- For i = 3 To UBound(brr)
- zf = brr(i, 1) & "," & brr(i, 2)
- x = Split(d(zf), ",")
- y = brr(i, 3)
- p = ""
- For j = 1 To UBound(x)
- If arr(x(j), 5) < y Then
- y = y - arr(x(j), 5): arr(x(j), 5) = 0
- Else
- arr(x(j), 5) = arr(x(j), 5) - y
- End If
- If arr(x(j), 5) > 0 Then p = p & "," & x(j)
- Next
- d(zf) = p
- Next
- For i = 2 To UBound(arr)
- crr(i - 1, 1) = arr(i, 1)
- crr(i - 1, 2) = arr(i, 2)
- crr(i - 1, 3) = arr(i, 4)
- crr(i - 1, 4) = arr(i, 5)
- Next
- Range("a2").Resize(UBound(crr), 4) = crr
- End Sub
复制代码 |
|