|
发表于 2013-7-1 13:41
|
显示全部楼层
本楼为最佳答案
附件请测试
注:测试按钮在TEST工作表内,稍微加了一些测试数据。- Private Sub CommandButton1_Click()
- Dim arr, i&, j%, d As Object
- Set d = CreateObject("scripting.dictionary")
- For j = 1 To [iv3].End(1).Column Step 2
- arr = Range(Cells(3, j), Cells(Cells(65536, j).End(3).Row, j + 1))
- Range(Cells(6, j), Cells(Cells(65536, j).End(3).Row, j + 1)).ClearContents
- For i = 4 To UBound(arr)
- If arr(2, 1) - arr(i, 2) >= 0 Then
- arr(2, 1) = arr(2, 1) - arr(i, 2)
- arr(i, 2) = ""
- Else
- arr(i, 2) = arr(i, 2) - arr(2, 1)
- Exit For
- End If
- Next i
- For i = UBound(arr) To 4 Step -1
- If arr(2, 2) - arr(i, 2) >= 0 Then
- arr(2, 2) = arr(2, 2) - arr(i, 2)
- arr(i, 2) = ""
- Else
- arr(i, 2) = arr(i, 2) - arr(2, 2)
- Exit For
- End If
- Next i
- For i = 4 To UBound(arr)
- If arr(i, 2) <> "" Then d(arr(i, 1)) = arr(i, 2)
- Next i
- Cells(6, j).Resize(d.Count, 1) = Application.Transpose(d.keys)
- Cells(6, j + 1).Resize(d.Count, 1) = Application.Transpose(d.items)
- d.RemoveAll
- Next j
- End Sub
复制代码 |
|