|
- Sub Macro1()
- Dim arr, brr, crr, drr, d, i&
- Set d = CreateObject("scripting.dictionary")
- Sheet2.Activate
- arr = Sheet1.Range("a1").CurrentRegion
- brr = Range("a5").CurrentRegion
- ReDim crr(1 To UBound(brr) - 4, 1 To 1)
- ReDim drr(1 To UBound(brr) - 4, 1 To 1)
- For i = 2 To UBound(arr)
- d(arr(i, 1)) = arr(i, 17)
- Next
- For i = 6 To UBound(brr)
- If d(brr(i, 6)) = 0 Then GoTo line100
- If d(brr(i, 6)) >= brr(i, 19) Then
- crr(i - 5, 1) = brr(i, 19)
- d(brr(i, 6)) = d(brr(i, 6)) - brr(i, 19)
- Else
- crr(i - 5, 1) = d(brr(i, 6)): d(brr(i, 6)) = 0
- End If
- If crr(i - 5, 1) > 0 Then drr(i - 5, 1) = Format(crr(i - 5, 1) / brr(i, 19), "0.00%")
- line100:
- Next
- Range("t6").Resize(UBound(crr)) = crr
- Range("v6").Resize(UBound(drr)) = drr
- End Sub
复制代码 |
|