'根据提示把数据补全,不然无法排序,,,
Option Explicit
Sub test()
Dim i, j, k, t, arr
arr = Sheets("sheet2").UsedRange
ReDim dic(5)
For i = 0 To UBound(dic)
Set dic(i) = CreateObject("scripting.dictionary")
Next
For j = 1 To UBound(arr, 2)
dic(0)(j) = arr(1, j)
dic(5)(arr(1, j)) = j
For i = 3 To UBound(arr, 1)
If Len(arr(i, j)) = 0 Then Exit For
dic(j)(arr(i, j)) = j
Next i, j
Sheets("sheet1").Activate
arr = Range("a6:n" & Cells(Rows.Count, "a").End(xlUp).Row)
For i = 1 To UBound(arr, 1)
For j = 1 To UBound(dic)
If dic(j).exists(arr(i, 1)) Then
arr(i, 5) = dic(0)(dic(j)(arr(i, 1)))
Exit For
End If
Next
If j = UBound(dic) + 1 Then
[a6].Resize(UBound(arr, 1), UBound(arr, 2)) = arr
MsgBox arr(i, 1): Exit Sub
End If
Next
Call bsort(arr, 1, UBound(arr, 1), 1, UBound(arr, 2), 5, dic(5))
[a6].Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub
Function bsort(arr, first, last, left, right, key, dic)
Dim i, j, k, t
For i = first To last - 1
For j = first To last + first - 1 - i
If dic(arr(j, key)) > dic(arr(j + 1, key)) Then
For k = left To right
t = arr(j, k): arr(j, k) = arr(j + 1, k): arr(j + 1, k) = t
Next
End If
Next j, i
End Function |