|
- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("sheet1")
- r = .Cells(1, 1).End(xlDown).Row
- arr = .Range("a2:f" & r)
- ReDim brr(1 To UBound(arr), 1 To 1)
- n = 0
- For i = 1 To UBound(arr)
- For j = 4 To 1 Step -1
- If Len(arr(i, j)) <> 0 Then
- If Not d.Exists(arr(i, j)) Then
- n = n + 1000
- d(arr(i, j)) = n
- End If
- Exit For
- End If
- Next
- brr(i, 1) = d(arr(i, j)) - j
- Next
- .Range("g2").Resize(UBound(brr), 1) = brr
- .Range("a2:g" & r).Sort key1:=.Cells(1, 7), order1:=xlAscending, header:=xlNo
- .Columns("g:g").ClearContents
- End With
- End Sub
复制代码 |
|