|
全部程序:
- Sub 班级课表()
- Dim i%, j%, k%, r%, t%, arr, brr()
- r = 1
- With Sheets("全校课表")
- arr = .UsedRange
- For j = 1 To UBound(arr, 2)
- If arr(4, j) = "星期二" Then t = j - 2: Exit For
- Next
- End With
- With Sheets("班级课表")
- .UsedRange.Clear
- .Rows("1:" & UBound(arr) * (t + 3)).RowHeight = 30
- .Range("A1:G" & UBound(arr) * (t + 3)).Font.Name = "宋体"
- .Range("A1:G" & UBound(arr) * (t + 3)).Font.Size = 10
- .Range("A1:G" & UBound(arr) * (t + 3)).HorizontalAlignment = xlCenter
- .Range("A1:G" & UBound(arr) * (t + 3)).VerticalAlignment = xlCenter
- .Columns("A:B").ColumnWidth = 8.43
- .Columns("C:G").ColumnWidth = 21.86
- For i = 6 To UBound(arr)
- ReDim brr(1 To t + 2, 1 To 6)
- brr(1, 1) = arr(i, 1) & "课表"
- For j = 2 To 6
- brr(2, j) = "星期" & Application.Text(j - 1, "[DBNum1]")
- Next
- For j = 3 To UBound(brr)
- brr(j, 1) = arr(5, j - 1)
- Next
- For j = 2 To UBound(arr, 2) Step t
- For k = 1 To t
- brr(k + 2, Int(j / t) + 2) = arr(i, j + k - 1)
- Next
- Next
- .Range("B" & r).Resize(1, 6).Merge
- .Range("B" & r).Font.Size = 20
- .Range("B" & r).Resize(UBound(brr), UBound(brr, 2)) = brr
- .Range("B" & r + 1 & ":G" & r + UBound(brr) - 1).Borders.LineStyle = xlContinuous
- r = .Cells(.Rows.Count, 2).End(xlUp).Row + 2
- Next
- End With
- End Sub
- Sub 教师课表()
- Dim i%, j%, k%, r%, t%, d, arr, brr()
- Set d = CreateObject("Scripting.Dictionary")
- r = 1
- With Sheets("全校课表")
- arr = .UsedRange
- For j = 1 To UBound(arr, 2)
- If arr(4, j) = "星期二" Then t = j - 2: Exit For
- Next
- For i = 6 To UBound(arr)
- For j = 2 To UBound(arr, 2) Step t
- For k = 1 To t
- If UBound(Split(arr(i, j + k - 1), Chr(10))) > 0 Then
- If Len(Split(arr(i, j + k - 1), Chr(10))(1)) Then
- If InStr(arr(i, j + k - 1), ":") > 0 Then
- If Not d.Exists(Split(Split(arr(i, j + k - 1), Chr(10))(1), ":")(1)) Then Set d(Split(Split(arr(i, j + k - 1), Chr(10))(1), ":")(1)) = CreateObject("Scripting.Dictionary")
- d(Split(Split(arr(i, j + k - 1), Chr(10))(1), ":")(1))(arr(4, j) & arr(5, j + k - 1)) = Array(Split(Split(arr(i, j + k - 1), Chr(10))(0), ":")(0) & "(" & Split(Split(arr(i, j + k - 1), Chr(10))(1), ":")(0) & ")", Split(arr(i, j + k - 1), Chr(10))(1), arr(i, 1))
- Else
- If Not d.Exists(Split(arr(i, j + k - 1), Chr(10))(1)) Then Set d(Split(arr(i, j + k - 1), Chr(10))(1)) = CreateObject("Scripting.Dictionary")
- d(Split(arr(i, j + k - 1), Chr(10))(1))(arr(4, j) & arr(5, j + k - 1)) = Array(Split(arr(i, j + k - 1), Chr(10))(0), Split(arr(i, j + k - 1), Chr(10))(1), arr(i, 1))
- End If
- End If
- End If
- Next
- Next
- Next
- End With
- With Sheets("教师课表")
- .UsedRange.Clear
- .Rows("1:" & d.Count * (t + 3)).RowHeight = 30
- .Range("A1:G" & d.Count * (t + 3)).Font.Name = "宋体"
- .Range("A1:G" & d.Count * (t + 3)).Font.Size = 10
- .Range("A1:G" & d.Count * (t + 3)).HorizontalAlignment = xlCenter
- .Range("A1:G" & d.Count * (t + 3)).VerticalAlignment = xlCenter
- .Columns("A:B").ColumnWidth = 8.43
- .Columns("C:G").ColumnWidth = 21.86
- For Each kk In d.Keys
- ReDim brr(1 To t + 2, 1 To 6)
- brr(1, 1) = kk & "课表"
- For j = 2 To 6
- brr(2, j) = "星期" & Application.Text(j - 1, "[DBNum1]")
- Next
- For j = 3 To UBound(brr)
- brr(j, 1) = arr(5, j - 1)
- Next
- For i = 2 To UBound(brr)
- For j = 2 To 6
- If d(kk).Exists(brr(2, j) & brr(i, 1)) Then brr(i, j) = d(kk)(brr(2, j) & brr(i, 1))(0) & Chr(10) & d(kk)(brr(2, j) & brr(i, 1))(2)
- Next
- Next
- .Range("B" & r).Resize(1, 6).Merge
- .Range("B" & r).Font.Size = 20
- .Range("B" & r).Resize(UBound(brr), UBound(brr, 2)) = brr
- .Range("B" & r + 1 & ":G" & r + UBound(brr) - 1).Borders.LineStyle = xlContinuous
- r = .Cells(.Rows.Count, 2).End(xlUp).Row + 2
- Next
- End With
- End Sub
复制代码
|
|