|
Public Sub test()
Dim arr, MyArray, dic As Object
Set dic = CreateObject("scripting.dictionary")
Set dic2 = CreateObject("scripting.dictionary")
With Sheet1
arr = .Range("a1").CurrentRegion.Value
'先去重
For i = 2 To UBound(arr, 2)
dic(arr(2, i)) = ""
Next i
MyArray = dic.keys
' 获取数组的长度排序
n = UBound(MyArray)
For i = LBound(MyArray) To n
For j = LBound(MyArray) To n - i - 1
If MyArray(j) < MyArray(j + 1) Then
' 交换元素
Temp = MyArray(j)
MyArray(j) = MyArray(j + 1)
MyArray(j + 1) = Temp
End If
Next j
Next i
For i = LBound(MyArray) To UBound(MyArray)
dic2(MyArray(i)) = i + 1
Next
For i = 2 To UBound(arr, 2)
arr(3, i) = dic2(arr(2, i))
Next
.Range("a1").Resize(UBound(arr), UBound(arr, 2)) = arr
End With
End Sub |
|