|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- Sub test99()
- Dim r%, i%
- Dim arr, brr
- Dim lk(1 To 4)
- Dim d As Object
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("scripting.dictionary")
- Set d_cs = CreateObject("scripting.dictionary")
- Set d_qk = CreateObject("scripting.dictionary")
- Set d_kc = CreateObject("scripting.dictionary")
- With Worksheets("缺考登记")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:g" & r)
- For i = 1 To UBound(arr)
- arr(i, 2) = Replace(arr(i, 2), ",", ",")
- xm = Split(arr(i, 2), ",")
- For j = 0 To UBound(xm)
- If Not d_qk.exists(arr(i, 1)) Then
- Set d_qk(arr(i, 1)) = CreateObject("scripting.dictionary")
- End If
- If Not d_qk(arr(i, 1)).exists(xm(j)) Then
- Set d_qk(arr(i, 1))(xm(j)) = CreateObject("scripting.dictionary")
- End If
- If Not d_qk(arr(i, 1))(xm(j)).exists(arr(i, 3)) Then
- Set d_qk(arr(i, 1))(xm(j))(arr(i, 3)) = CreateObject("scripting.dictionary")
- End If
- d_qk(arr(i, 1))(xm(j))(arr(i, 3))(arr(i, 4)) = ""
- Next
- Next
- End With
- With Worksheets("考场安排")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:f" & r)
- For j = 3 To 5
- For i = 3 To UBound(arr)
- If Len(arr(i, j)) <> 0 And arr(i, j) <> 0 Then
- If Not d_kc.exists(arr(2, j)) Then
- Set d_kc(arr(2, j)) = CreateObject("scripting.dictionary")
- End If
- d_kc(arr(2, j))(arr(i, 1)) = Array(arr(i, 2), arr(i, j))
- End If
- Next
- Next
- End With
- With Worksheets("参数设置")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:c" & r)
- For i = 1 To UBound(arr)
- If arr(i, 3) = "是" Then
- If Not d.exists(arr(i, 1)) Then
- Set d(arr(i, 1)) = CreateObject("scripting.dictionary")
- End If
- If Not d(arr(i, 1)).exists(arr(i, 2)) Then
- Set d(arr(i, 1))(arr(i, 2)) = CreateObject("scripting.dictionary")
- End If
- If d_kc.exists(arr(i, 1)) Then
- For Each bb In d_kc(arr(i, 1)).keys
- crr = d_kc(arr(i, 1))(bb)
- If Not d(arr(i, 1))(arr(i, 2)).exists(bb) Then
- Set d(arr(i, 1))(arr(i, 2))(bb) = CreateObject("scripting.dictionary")
- End If
- d(arr(i, 1))(arr(i, 2))(bb) = crr '年级+科目+试场
- Next
- End If
- End If
- Next
- End With
- With Worksheets("模板")
- For j = 1 To 4
- lk(j) = .Columns(j).ColumnWidth
- Next
- For Each aa In d.keys
- m = 1
- n = 1
- .Rows("26:" & .Rows.Count).Delete
- .Columns(5).Resize(, .Columns.Count - 4).Delete
- For i = 1 To 11 Step 5
- For j = 1 To 4
- .Columns(i + j - 1).ColumnWidth = lk(j)
- Next
- If i <> 11 Then
- .Columns(i + 4).ColumnWidth = 15.25
- End If
- Next
- For Each bb In d(aa).keys
- For Each cc In d(aa)(bb).keys
- If m <> 1 Or n <> 1 Then
- .Range("a1:d25").Copy .Cells(m, n)
- End If
- crr = d(aa)(bb)(cc)
- .Cells(m + 1, n + 1) = aa
- .Cells(m + 1, n + 3) = bb
- .Cells(m + 2, n + 1) = cc
- .Cells(m + 2, n + 3) = crr(0)
- .Cells(m + 4, n).Resize(20, 4).ClearContents
- For i = 1 To crr(1)
- .Cells(IIf(i <= 20, m + i + 3, m + i - 17), IIf(i <= 20, n, n + 2)) = i
- If d_qk.exists(aa) Then
- If d_qk(aa).exists(bb) Then
- If d_qk(aa)(bb).exists(cc) Then
- If d_qk(aa)(bb)(cc).exists(i) Then
- .Cells(IIf(i <= 20, m + i + 3, m + i - 17), IIf(i <= 20, n + 1, n + 3)) = "缺考"
- End If
- End If
- End If
- End If
- Next
- n = n + 5
- If n > 11 Then
- n = 1
- m = m + 25
- End If
- Next
- Next
- .Copy
- With ActiveWorkbook
- .SaveAs Filename:=ThisWorkbook.Path & "" & aa & "登分卡"
- .Close False
- End With
- Next
- End With
- Application.ScreenUpdating = True
- MsgBox "登分卡生成完毕!"
-
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|