- Sub Macro1()
- Dim arr, brr, d, i&, j%, zf$, z$
- Set d = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("a2").CurrentRegion
- brr = Sheet2.Range("a2").CurrentRegion
- For i = 2 To UBound(arr)
- zf = arr(i, 1) & "," & arr(i, 2) & "," & arr(i, 3)
- For j = 4 To UBound(arr, 2)
- If arr(i, j) <> "" Then d(zf & "," & arr(1, j)) = arr(i, j)
- Next
- Next
- For i = 2 To UBound(brr)
- zf = brr(i, 3) & "," & brr(i, 4) & "," & brr(i, 5)
- For j = 6 To UBound(brr, 2)
- z = zf & "," & brr(1, j)
- If d.exists(z) Then
- If d(z) > brr(i, j) Then
- d(z) = d(z) - brr(i, j)
- Else
- brr(i, j) = d(z): d(z) = 0
- End If
- End If
- Next
- Next
- Sheet3.Range("a1").Resize(UBound(brr), UBound(brr, 2)) = brr
- End Sub
复制代码 |