|
- Sub 排序后对冲()
- Set d = CreateObject("scripting.dictionary")
- r = [a65536].End(3).Row
- Range("a1:b" & r).Copy [f1]
- Range("f2:g" & r).Sort key1:=[f2]
- arr = Range("f1:g" & r)
- For i = 2 To UBound(arr)
- x = "'" & arr(i, 1)
- d(x) = d(x) & "+" & i
- Next
- dk = d.keys: dt = d.items
- For i = 0 To UBound(dk)
- x = Split(dt(i), "+")
- s1 = Val(x(1)): s2 = Val(x(UBound(x)))
- For j = s1 To s2 - 1
- For k = j + 1 To s2
- If arr(j, 2) + arr(k, 2) = 0 Then
- arr(k, 2) = 0: arr(j, 2) = 0
- Exit For
- End If
- Next
- Next
- Next
- ReDim brr(1 To UBound(arr), 1 To 2)
- brr(1, 1) = arr(1, 1): brr(1, 2) = arr(1, 2)
- p = 1
- For i = 2 To UBound(arr)
- If arr(i, 2) <> 0 Then
- p = p + 1
- brr(p, 1) = arr(i, 1)
- brr(p, 2) = arr(i, 2)
- End If
- Next
- Range("f:g").ClearContents
- [f1].Resize(p, 2) = brr
-
- End Sub
复制代码 |
|