|
附件请测试- Private Sub CommandButton1_Click()
- Dim arr, i&, d As Object, d1 As Object
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- arr = Range("b2:b" & [b65536].End(3).Row)
- ReDim Preserve arr(1 To UBound(arr), 1 To 3)
- For i = 1 To UBound(arr)
- If d.exists(arr(i, 1)) Then arr(i, 2) = i - d(arr(i, 1)) - 1 Else arr(i, 2) = i - 1
- d(arr(i, 1)) = i
- If d1.exists(arr(i, 1)) Then arr(i, 3) = d1(arr(i, 1)) Else arr(i, 3) = ""
- d1(arr(i, 1)) = arr(i, 2)
- Next i
- [b2].Resize(UBound(arr), 3) = arr
- End Sub
复制代码 |
|