|
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Count > 1 Then Exit Sub
- If Target = "" Then Exit Sub
- If Target.Address <> "$C$2" Then Exit Sub
- Dim i&, Myr&, y&, ye%, j&, aa, Arr, d, rng As Range, n%, c%, m%, mm%, nn%, xx$
- Set d = CreateObject("Scripting.Dictionary")
- Set rng = Sheet3.[q1:r4]
- xx = [b2].Value
- Myr = Sheet1.[a65536].End(xlUp).Row
- Arr = Sheet1.Range("a1:d" & Myr)
- For i = 2 To UBound(Arr)
- If Arr(i, 4) <> "" Then d(Arr(i, 4)) = d(Arr(i, 4)) & i & ","
- Next
- t = d(Target.Value)
- If t = "" Then MsgBox "没有这个班级的数据!": Exit Sub
- t = Left(t, Len(t) - 1)
- If InStr(t, ",") Then
- aa = Split(t, ",")
- y = (UBound(aa) + 1) Mod 12
- If y = 0 Then
- ye = Int((UBound(aa) + 1) / 12)
- Else
- ye = Int((UBound(aa) + 1) / 12) + 1
- End If
- For j = 1 To ye
- Sheet3.[a1:l15].ClearContents
- Sheet3.[a1:l15].Borders.LineStyle = xlNone
- Do
- n = n + 1: nn = nn + 1
- c = n Mod 4: mm = Int(n / 4)
- If c <> 0 Then
- col = 3 * c - 1
- m = 5 * mm + 1
- If m > 11 Then n = 0: nn = nn - 1: Exit Do
- Else
- col = 11
- End If
- rng.Copy Sheet3.Cells(m, col)
- With Sheet3
- .Cells(m, col).Value = xx
- .Cells(m + 1, col + 1).Value = Arr(aa(nn - 1), 4)
- .Cells(m + 2, col + 1).Value = Arr(aa(nn - 1), 2)
- .Cells(m + 3, col + 1).Value = Arr(aa(nn - 1), 1)
- End With
- Loop While nn < UBound(aa) + 1
- Sheet3.[a1:l15].PrintOut
- Next
- End If
- End Sub
复制代码 |
|