|
本帖最后由 liuguansky 于 2012-3-8 11:55 编辑
效果详见附件:
单击Sheet1的按钮[每个学院分表显示]
- Sub justtest()
- Dim D As New Dictionary, Arr, i&, BTAr, Sh As Worksheet, K&, M&, N&
- Dim Ar(1 To 4), k1&, k2&, k3&, k4&, ArRe(1 To 60000, 1 To 3) As String
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Arr = Range("A1").CurrentRegion.Value
- For i = 2 To UBound(Arr)
- If D.Exists(Arr(i, 1)) Then
- If D(Arr(i, 1)).Exists(Arr(i, 2)) Then
- If D(Arr(i, 1))(Arr(i, 2)).Exists(Arr(i, 3)) Then
- If Not D(Arr(i, 1))(Arr(i, 2))(Arr(i, 3)).Exists(Arr(i, 4)) Then
- D(Arr(i, 1))(Arr(i, 2))(Arr(i, 3)).Add Arr(i, 4), ""
- End If
- Else
- D(Arr(i, 1))(Arr(i, 2)).Add Arr(i, 3), ""
- Set D(Arr(i, 1))(Arr(i, 2))(Arr(i, 3)) = New Dictionary
- End If
- Else
- D(Arr(i, 1)).Add Arr(i, 2), ""
- Set D(Arr(i, 1))(Arr(i, 2)) = New Dictionary
- End If
- Else
- D.Add Arr(i, 1), ""
- Set D(Arr(i, 1)) = New Dictionary
- End If
- Next i
- For Each Sh In Worksheets
- If Sh.Name <> "Sheet1" Then
- Sh.Delete
- End If
- Next Sh
- BTAr = Array("学院", "系别", "班级")
- Ar(1) = D.Keys
- For k1 = 0 To UBound(Ar(1))
- K = 0
- With Worksheets.Add(after:=Worksheets(Worksheets.Count))
- .Name = Ar(1)(k1)
- .[a1] = Ar(1)(k1)
- .[a2:c2] = BTAr
- Ar(2) = D(Ar(1)(k1)).Keys
- For k2 = 0 To UBound(Ar(2))
- M = K
- K = K + 1: ArRe(K, 1) = Ar(2)(k2)
- Ar(3) = D(Ar(1)(k1))(Ar(2)(k2)).Keys
- For k3 = 0 To UBound(Ar(3))
- N = M
- M = M + 1: ArRe(M, 2) = Ar(3)(k3)
- Ar(4) = D(Ar(1)(k1))(Ar(2)(k2))(Ar(3)(k3)).Keys
- For k4 = 0 To UBound(Ar(4))
- N = N + 1: ArRe(N, 3) = Ar(4)(k4)
- Next
- Next
- K = N
- Next
- .[a3].Resize(N, 3) = ArRe
- .[a:c].EntireColumn.AutoFit
- End With
- Next
- Sheet1.Activate
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- MsgBox "处理完毕。"
- End Sub
复制代码
学生类型.rar
(106.75 KB, 下载次数: 2)
|
|