Sub arrange()
Dim wsSource As Worksheet
Dim wsArrange As Worksheet
Dim wsTarget As Worksheet
Dim rng As Range
Dim lastRow As Integer, lastCol As Integer
Dim arrArng(), arr(), iRow As Integer, Lines As Integer, iCol As Integer
Set wsSource = Sheets("数据")
Set wsArrange = Sheets("安排")
Set wsTarget = Sheets("结果")
With wsSource
lastRow = .UsedRange.Rows.Count
lastCol = .UsedRange.Columns.Count
arr = .Range(.Cells(2, 1), .Cells(lastRow, lastCol)).Value
End With
arrArng = wsArrange.UsedRange
With wsTarget
.Activate
.Cells.ClearContents
wsTarget.Cells(1, 1) = "讲台"
For i = 2 To UBound(arrArng)
If arrArng(i, 1) <> "" Then
iRow = .UsedRange.Rows.Count + 1
If iCol < arrArng(i, 3) Then
iCol = arrArng(i, 3)
End If
iRow = iRow + 1
.Cells(iRow, 1) = "第" & arrArng(i, 1) & "考场"
Lines = Application.WorksheetFunction.RoundUp(arrArng(i, 2) / arrArng(i, 3), 0)
n = 0
For j = 1 To Lines
For k = 1 To arrArng(i, 3)
m = m + 1
n = n + 1
If m = UBound(arr) Then
GoTo Exitline
End If
.Cells(iRow + j, k) = j & k & "." & arr(m, 3)
If n = arrArng(i, 2) Then
GoTo NextRoom
End If
Next
Next
End If
NextRoom:
Next
Set rng = .Range(.Cells(1, 1), .Cells(1, iCol))
rng.Select
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.Font.Size = 12
.RowHeight = 20
End With
End With
Exitline:
MsgBox "共" & UBound(arr) & "人,安排完成 " & m & "人!"
End Sub