|
- Sub 调整格式()
- Dim vData As Variant, nRow As Integer, nCol As Integer
- Dim oDic As Object
- Dim sWeek As String, nClass As Integer, nLesson As Integer, sTeacher As String
- Dim vFill As Variant
-
- Set oDic = CreateObject("Scripting.Dictionary")
- vData = Sheet1.[A19].CurrentRegion.Value
- For nCol = 4 To UBound(vData, 2)
- If vData(1, nCol) <> "" Then sWeek = Trim(vData(1, nCol))
- If vData(2, nCol) <> "" Then nClass = Val(vData(2, nCol))
- For nRow = 3 To UBound(vData)
- nLesson = Val(vData(nRow, 3))
- If vData(nRow, nCol) <> "" Then
- sTeacher = Trim(vData(nRow, nCol))
- oDic(sTeacher & "|" & sWeek & "|" & nLesson) = nClass
- End If
- Next
- Next
- vData = Sheet3.UsedRange.Value
- ReDim vFill(3 To UBound(vData), 3 To UBound(vData, 2))
- For nCol = 3 To UBound(vData, 2)
- If vData(2, nCol) <> "" Then
- sWeek = Trim(vData(2, nCol))
- nLesson = 1
- Else
- nLesson = nLesson + 1
- End If
- For nRow = 3 To UBound(vData)
- sTeacher = Trim(vData(nRow, 1))
- If oDic.Exists(sTeacher & "|" & sWeek & "|" & nLesson) Then vFill(nRow, nCol) = oDic(sTeacher & "|" & sWeek & "|" & nLesson)
- Next
- Next
- Sheet3.[C3].Resize(UBound(vFill) - 2, UBound(vFill, 2) - 2) = vFill
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|