'看不太懂,先猜一个,,,
Option Explicit
Sub test()
Dim arr, i, j, k, t, p, n
With Sheets("抽签表 (理想结果)")
arr = .Range("a3:d" & .Cells(Rows.Count, "b").End(xlUp).Row + 1)
For i = 1 To UBound(arr, 1) - 1
arr(i, 3) = Val(Mid(arr(p + 1, 1), 2))
n = n + 1: arr(i, 4) = n
If Len(arr(i + 1, 1)) > 0 Or i = UBound(arr, 1) - 1 Then p = i: n = 0
Next
For i = 1 To UBound(arr, 1) - 2
For j = i + 1 To UBound(arr, 1) - 1
If StrComp(arr(i, 2), arr(j, 2), vbTextCompare) = 1 Then
For k = 1 To UBound(arr, 2)
t = arr(i, k): arr(i, k) = arr(j, k): arr(j, k) = t
Next
End If
Next
Next
For i = 1 To UBound(arr, 1) - 1
arr(i, 1) = arr(i, 2): arr(i, 2) = arr(i, 3)
arr(i, 3) = Format(arr(i, 4), "第0个") & arr(i, 2)
Next
.[e3].Resize(UBound(arr, 1), 3) = arr
End With
End Sub |