条件判断很多,看代码眼花缭乱,你先测试一下:
- 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 InStr(arr(i, j + k - 1), ":") > 0 Then '有冒号
- If Len(Split(arr(i, j + k - 1), Chr(10))(0)) And Len(Split(arr(i, j + k - 1), Chr(10))(1)) Then '回车符的前后两部分都有内容
- If Split(Split(arr(i, j + k - 1), Chr(10))(0), ":")(1) = Split(Split(arr(i, j + k - 1), Chr(10))(1), ":")(1) 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(Split(arr(i, j + k - 1), Chr(10))(0), ":")(1)) Then Set d(Split(Split(arr(i, j + k - 1), Chr(10))(0), ":")(1)) = CreateObject("Scripting.Dictionary")
- If Not d(Split(Split(arr(i, j + k - 1), Chr(10))(0), ":")(1)).Exists(arr(4, j) & arr(5, j + k - 1)) Then
- d(Split(Split(arr(i, j + k - 1), Chr(10))(0), ":")(1))(arr(4, j) & arr(5, j + k - 1)) = Array(Split(Split(arr(i, j + k - 1), Chr(10))(0), ":")(0), Split(arr(i, j + k - 1), Chr(10))(1), arr(i, 1)) '单周
- Else
- d(Split(Split(arr(i, j + k - 1), Chr(10))(0), ":")(1))(arr(4, j) & arr(5, j + k - 1)) = Array(d(Split(Split(arr(i, j + k - 1), Chr(10))(0), ":")(1))(arr(4, j) & arr(5, j + k - 1))(0) & Chr(10) & d(Split(Split(arr(i, j + k - 1), Chr(10))(0), ":")(1))(arr(4, j) & arr(5, j + k - 1))(2), Split(arr(i, j + k - 1), Chr(10))(1), Split(Split(arr(i, j + k - 1), Chr(10))(0), ":")(0) & Chr(10) & arr(i, 1)) '单双周
- End If
- 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")
- If Not d(Split(Split(arr(i, j + k - 1), Chr(10))(1), ":")(1)).Exists(arr(4, j) & arr(5, j + k - 1)) Then
- 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))(1), ":")(0), Split(arr(i, j + k - 1), Chr(10))(1), arr(i, 1)) '双周
- Else
- d(Split(Split(arr(i, j + k - 1), Chr(10))(1), ":")(1))(arr(4, j) & arr(5, j + k - 1)) = Array(d(Split(Split(arr(i, j + k - 1), Chr(10))(1), ":")(1))(arr(4, j) & arr(5, j + k - 1))(0) & Chr(10) & d(Split(Split(arr(i, j + k - 1), Chr(10))(1), ":")(1))(arr(4, j) & arr(5, j + k - 1))(2), Split(arr(i, j + k - 1), Chr(10))(1), Split(Split(arr(i, j + k - 1), Chr(10))(1), ":")(0) & Chr(10) & arr(i, 1)) '单双周
- End If
- End If
- ElseIf Len(Split(arr(i, j + k - 1), Chr(10))(0)) Then '前半部分有内容
- If Not d.Exists(Split(Split(arr(i, j + k - 1), Chr(10))(0), ":")(1)) Then Set d(Split(Split(arr(i, j + k - 1), Chr(10))(0), ":")(1)) = CreateObject("Scripting.Dictionary")
- d(Split(Split(arr(i, j + k - 1), Chr(10))(0), ":")(1))(arr(4, j) & arr(5, j + k - 1)) = Array(Split(Split(arr(i, j + k - 1), Chr(10))(0), ":")(0), Split(arr(i, j + k - 1), Chr(10))(1), arr(i, 1))
- ElseIf Len(Split(arr(i, j + k - 1), Chr(10))(1)) 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))(1), ":")(0), Split(arr(i, j + k - 1), Chr(10))(1), arr(i, 1))
- End If
- Else
- If Len(Split(arr(i, j + k - 1), Chr(10))(1)) Then
- 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 = 52.5
- .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
- If Len(Trim(kk)) Then
- 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
- End If
- Next
- End With
- End Sub
复制代码
|