- <P>Sub text2()
- Dim arr(), i As Long, r As Long, d As Object, g(), k As Long, arr1(1 To 60000, 1 To 9)
- With Sheet1
- r = .Cells(Rows.Count, 3).End(3).Row
- arr = Range("c5:t" & r).Value
- End With
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- For i = 1 To r - 4
- If d1.exists(arr(i, 18)) Then
- d1(arr(i, 18)) = d1(arr(i, 18)) + arr(i, 9)
- Else
- d1(arr(i, 18)) = arr(i, 9)
- End If
- If arr(i, 4) = "退" Or arr(i, 4) = "赠" Then
- d(arr(i, 18)) = ""
- End If
- Next
- g = d.keys
- x = d1.items
- For i = 1 To r - 4
- For j = 1 To d.Count
- If arr(i, 18) = g(j - 1) And d1(arr(i, 18)) <> 0 Then
- k = k + 1
- arr1(k, 1) = arr(i, 18)
- arr1(k, 2) = arr(i, 1)
- arr1(k, 3) = arr(i, 2)
- arr1(k, 4) = arr(i, 3)
- arr1(k, 5) = arr(i, 4)
- arr1(k, 6) = arr(i, 13)
- arr1(k, 7) = arr(i, 7)
- arr1(k, 8) = arr(i, 16)
- arr1(k, 9) = arr(i, 9)
- End If
- Next
- Next
- Range("V5:AD65536").ClearContents
- Range("V5").Resize(k, 9) = arr1
- Range("v5:ad" & k).Sort Key1:=Range("V5"), Key2:=Range("Z5"), Order2:=xlDescending
- End Sub
- </P>
复制代码 试试这个吧~~~
|