本帖最后由 一把小刀闯天下 于 2019-11-28 21:06 编辑
'消息收到,看不太懂
'第2次通过的没有看到>=90的分数,按第1次通过生成的编号
'如果真有第n次通过的可把第n-1到第1次通过的人的分数置-1,编号从第n-1最大编号+1开始
'没注意还要带班级输出的,稍作修改,,,
Option Explicit
Sub test()
Dim arr, y, m, mark, i, j
mark = Split("?,一,二,三,四,五,六,七,八,九", ",")
arr = Range("a3:f" & Cells(Rows.Count, "b").End(xlUp).Row)
y = right(Year(Date), 2): m = Format(Month(Date), "00")
For i = 1 To UBound(arr, 1)
arr(i, 1) = vbNullString: arr(i, 3) = -i
If arr(i, 6) < 90 Then arr(i, 6) = -1
arr(i, 2) = Replace(arr(i, 2), "(", vbNullString)
arr(i, 2) = Replace(arr(i, 2), ")", vbNullString)
For j = 0 To UBound(mark)
arr(i, 2) = Replace(arr(i, 2), mark(j), j)
Next
Next
Call bsort(arr, 1, UBound(arr, 1), 1, UBound(arr, 2), 6)
For i = 1 To UBound(arr, 1)
If arr(i, 6) = -1 Then Exit For
arr(i, 1) = y & m & arr(i, 2) & Format(i, "000")
Next
Call bsort(arr, 1, UBound(arr, 1), 1, UBound(arr, 2), 3)
[j3].Resize(UBound(arr, 1)) = arr
End Sub
Function bsort(arr, first, last, left, right, key)
Dim i, j, k, t
For i = first To last - 1
For j = first To last + first - 1 - i
If arr(j, key) < 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
Next
End Function
|