本帖最后由 microyip 于 2019-1-13 12:50 编辑
- Sub Result()
- Dim vData As Variant, nRow As Integer, nCol As Integer, nPart As Integer
- Dim nWeek As Integer, nClass As Integer, sSubject As String
- Dim dicClass As Object, dicWeek As Object
- Dim vFill As Variant, nFill As Integer, nI As Integer
-
- Set dicWeek = CreateObject("Scripting.Dictionary")
- vData = Split("星期一,星期二,星期三,星期四,星期五,星期六,星期日", ",")
- For nPart = 0 To UBound(vData)
- dicWeek(vData(nPart)) = nPart + 1
- Next
- Set dicClass = CreateObject("Scripting.Dictionary")
- vData = Split("一,二,三,四,五,六,七,八,九,十,十一,十二,十三,十四,十五,十六,十七,十八,十九,二十", ",")
- For nPart = 0 To UBound(vData)
- dicClass(vData(nPart)) = nPart + 1
- Next
-
- ReDim vFill(1 To 4, 1 To 1)
- vData = Sheets("课程表").UsedRange.Value
- For nRow = 3 To UBound(vData)
- If dicClass.Exists(Trim(vData(nRow, 1))) Then
- nClass = dicClass(Trim(vData(nRow, 1)))
- For nCol = 2 To UBound(vData,2)
- sSubject = Trim(vData(nRow, nCol))
- If sSubject <> "" Then
- If dicWeek.Exists(Trim(vData(1, nCol))) Then nWeek = dicWeek(Trim(vData(1, nCol)))
- nPart = Val(vData(2, nCol))
- nFill = nFill + 1
- ReDim Preserve vFill(1 To 4, 1 To nFill)
- vFill(1, nFill) = sSubject
- vFill(2, nFill) = nClass
- vFill(3, nFill) = nWeek
- vFill(4, nFill) = nPart
- End If
- Next
- End If
- Next
-
- If nFill > 0 Then
- Application.ScreenUpdating = False
- With Sheets("课表结果")
- .UsedRange.Offset(1).ClearContents
- .[A2].Resize(nFill, 4) = Application.WorksheetFunction.Transpose(vFill)
- .[A1].Resize(nFill + 1, 4).Sort Key1:=.[A2], Order1:=xlAscending, Key2:=.[B2], Order2:=xlAscending, Key3:=.[C2], Order3:=xlAscending, Header:=xlGuess
- End With
- Application.ScreenUpdating = True
- End If
- End Sub
复制代码 |