|
- Sub Macro1()
- Dim arr, brr, crr, d, i&
- Set d = CreateObject("scripting.dictionary")
- arr = Range("a1").CurrentRegion
- ReDim brr(1 To UBound(arr) - 1, 1 To 1)
- crr = Range("e2").CurrentRegion
- For i = 2 To UBound(arr)
- s100 = arr(i, 2) \ 100
- d(100) = d(100) + s100
- If s100 <> 0 Then brr(i - 1, 1) = s100 & "×100+"
- s50 = (arr(i, 2) - s100 * 100) \ 50
- d(50) = d(50) + s50
- If s50 <> 0 Then brr(i - 1, 1) = brr(i - 1, 1) & s50 & "×50+"
- s20 = (arr(i, 2) - s100 * 100 - s50 * 50) \ 20
- d(20) = d(20) + s20
- If s20 <> 0 Then brr(i - 1, 1) = brr(i - 1, 1) & s20 & "×20+"
- s10 = (arr(i, 2) - s100 * 100 - s50 * 50 - s20 * 20) \ 10
- d(10) = d(10) + s10
- If s10 <> 0 Then brr(i - 1, 1) = brr(i - 1, 1) & s10 & "×10+"
- s5 = (arr(i, 2) - s100 * 100 - s50 * 50 - s20 * 20 - s10 * 10) \ 5
- d(5) = d(5) + s5
- If s5 <> 0 Then brr(i - 1, 1) = brr(i - 1, 1) & s5 & "×5+"
- s1 = arr(i, 2) Mod 5
- d(1) = d(1) + s1
- If s1 <> 0 Then brr(i - 1, 1) = brr(i - 1, 1) & s1 & "×1"
- Next
- For i = 1 To UBound(crr)
- crr(i, 1) = d(crr(i, 1))
- Next
- Range("c2").Resize(UBound(brr)) = brr
- Range("f2").Resize(UBound(crr)) = crr
- End Sub
复制代码 |
|