|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub zhunkaozheng()
Application.ScreenUpdating = False
Dim ar As Variant
Dim d As Object
Dim br()
Set d = CreateObject("scripting.dictionary")
Application.DisplayAlerts = False
For Each sh In Sheets
If sh.Index > 2 Then
sh.Delete
End If
Next sh
Application.DisplayAlerts = True
With Sheets("考生资料")
r = .Cells(Rows.Count, 1).End(xlUp).Row
If r < 2 Then MsgBox "考生资料为空!": End
ar = .Range("A1:H" & r)
End With
For i = 2 To UBound(ar)
If Trim(ar(i, 4)) <> "" Then
d(Trim(ar(i, 4))) = ""
End If
Next i
For Each k In d.keys
n = 0
ReDim br(1 To UBound(ar), 1 To UBound(ar, 2))
For i = 2 To UBound(ar)
If Trim(ar(i, 4)) = k Then
n = n + 1
For j = 1 To UBound(ar, 2)
br(n, j) = ar(i, j)
Next j
End If
Next i
Sheets("准考证").Copy after:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
.Name = "准考证-" & k
If n / 2 = Int(n / 2) Then
sl = n / 2
Else
sl = Int(n / 2) + 1
End If
m = 17
For i = 2 To sl
.Rows("1:16").Copy .Cells(m, 1)
m = m + 16
Next i
m = 4
For i = 1 To n Step 2
.Cells(m, 2) = br(i, 2)
.Cells(m + 1, 2) = br(i, 3)
.Cells(m + 2, 2) = br(i, 4)
.Cells(m + 3, 2) = br(i, 5)
.Cells(m + 4, 2) = br(i, 6)
.Cells(m + 4, 5) = br(i, 7)
If i + 1 <= n Then
.Cells(m, 9) = br(i + 1, 2)
.Cells(m + 1, 9) = br(i + 1, 3)
.Cells(m + 2, 9) = br(i + 1, 4)
.Cells(m + 3, 9) = br(i + 1, 5)
.Cells(m + 4, 9) = br(i + 1, 6)
.Cells(m + 4, 12) = br(i + 1, 7)
End If
m = m + 16
Next i
End With
Next k
Sheets("考生资料").Select
Application.ScreenUpdating = True
MsgBox "ok!"
End Sub |
评分
-
1
查看全部评分
-
|