本帖最后由 一把小刀闯天下 于 2019-1-17 22:10 编辑
'我想简单了。后来换了随机交换,跑了几分钟也没跑出结果,,,
Option Explicit
Sub test()
Dim arr, i, j, k, t, dic, key, p, a, b
Set dic = CreateObject("scripting.dictionary")
arr = Sheets("总课表").[c7:bp32]
For i = 3 To UBound(arr, 1) Step 3
For j = 3 To UBound(arr, 2)
If Len(arr(i, j)) Then
If dic.exists(arr(i, j)) Then
t = dic(arr(i, j))
ReDim Preserve t(UBound(t) + 4) '
t(UBound(t) - 3) = arr(i + 1, j) '
t(UBound(t) - 2) = arr(1, j)
t(UBound(t) - 1) = arr(i + 2, j)
t(UBound(t)) = i / 3
dic(arr(i, j)) = t
Else
dic(arr(i, j)) = Array(arr(i + 1, j), arr(1, j), arr(i + 2, j), i / 3)
End If
End If
Next j, i
With Sheets("安排")
arr = .Range("a2:h" & .Cells(Rows.Count, "a").End(xlUp).Row + 1)
For i = 1 To UBound(arr, 1) - 1
For j = 5 To 7: arr(i, j) = vbNullString: Next
arr(i, j) = i
Next
Call qsort(arr, 1, UBound(arr, 1) - 1, 1, UBound(arr, 2), 4)
For Each key In dic.Keys
t = dic(key)
For i = 1 To UBound(arr, 1) - 1
If arr(i, 4) = key Then p = i: Exit For
Next
If i = UBound(arr, 1) Then MsgBox "无法找到课程!": Exit Sub
For i = 0 To UBound(t) Step 4
For j = 1 To t(i)
For k = 5 To 7: arr(p, k) = t(i + k - 4): Next
p = p + 1
If arr(p - 1, 4) <> key Then
Call qsort(arr, 1, UBound(arr, 1) - 1, 1, UBound(arr, 2), 8)
.[a2].Resize(UBound(arr, 1) - 1, UBound(arr, 2) - 1) = arr
MsgBox key & vbNewLine & "人数不够排!": Exit Sub
End If
Next j, i, key
Call qsort(arr, 1, UBound(arr, 1) - 1, 1, UBound(arr, 2), 8)
For i = 1 To UBound(arr, 1) - 1
For j = i To UBound(arr, 1) - 1
If arr(j, 1) <> arr(j + 1, 1) Then
dic.RemoveAll
For a = i To j
If dic.exists(arr(a, 7)) Then
For b = j + 1 To UBound(arr, 1) - 1
If arr(a, 4) = arr(b, 4) And Not dic.exists(arr(b, 7)) Then
For k = 5 To 7
t = arr(a, k): arr(a, k) = arr(b, k): arr(b, k) = t
Next
dic(arr(a, 7)) = a
Exit For
End If
Next
If b = UBound(arr, 1) Then MsgBox "无法调整:" & i: Exit Sub
Else
dic(arr(a, 7)) = a
End If
Next
i = j: Exit For
End If
Next j, i
.[a2].Resize(UBound(arr, 1) - 1, UBound(arr, 2) - 1) = arr
End With
End Sub
Function qsort(arr, first, last, left, right, key)
Dim i As Long, j As Long, k As Long, x, t
i = first: j = last: x = arr((first + last) / 2, key)
While i <= j
While arr(i, key) < x: i = i + 1: Wend
While x < arr(j, key): j = j - 1: Wend
If i <= j Then
For k = left To right
t = arr(i, k): arr(i, k) = arr(j, k): arr(j, k) = t
Next
i = i + 1: j = j - 1
End If
Wend
If first < j Then Call qsort(arr, first, j, left, right, key)
If i < last Then Call qsort(arr, i, last, left, right, key)
End Function
|