|
附件请测试,如果是2010版,请将第4行代码的[iv1]的IV换成相应的最大列号
如果行数超过65536,还需要把代码内有65536的部分换成2010版的最大行数- Private Sub CommandButton1_Click()
- Dim arr, i&, j%, d As Object
- Set d = CreateObject("scripting.dictionary")
- For j = 1 To [iv1].End(1).Column Step 2
- arr = Range(Cells(2, j), Cells(Cells(65536, j).End(3).Row, j + 1))
- Range(Cells(5, j), Cells(Cells(65536, j).End(3).Row + 1, j + 1)).ClearContents
- For i = 4 To UBound(arr)
- If arr(i, 1) = "" Then Exit For
- If arr(2, 1) - arr(i, 1) >= 0 Then
- arr(2, 1) = arr(2, 1) - arr(i, 1)
- arr(i, 1) = ""
- Else
- arr(i, 1) = arr(i, 1) - arr(2, 1)
- Exit For
- End If
- Next i
- For i = UBound(arr) To 4 Step -1
- If arr(i, 1) = "" Then Exit For
- If arr(2, 2) - arr(i, 1) >= 0 Then
- arr(2, 2) = arr(2, 2) - arr(i, 1)
- arr(i, 1) = ""
- Else
- arr(i, 1) = arr(i, 1) - arr(2, 2)
- Exit For
- End If
- Next i
- For i = 4 To UBound(arr)
- If arr(i, 1) <> "" Then d(arr(i, 2)) = arr(i, 1)
- Next i
- If d.Count >= 1 Then
- Cells(5, j).Resize(d.Count, 1) = Application.Transpose(d.items)
- Cells(5, j + 1).Resize(d.Count, 1) = Application.Transpose(d.keys)
- d.RemoveAll
- Erase arr
- End If
- Next j
- End Sub
复制代码 |
|