|
楼主 |
发表于 2013-7-26 21:09
|
显示全部楼层
代码如下:
Sub 生成抽签登记表()
Const StartRow1 As Byte = 3
Const JumpNext As Byte = 25
Dim sRoom As String
Dim LastRow1 As Long, StartRow2 As Long, LastRow2 As Long, i1 As Long, i2 As Long
Application.ScreenUpdating = False
With Sheet1
LastRow1 = .Cells(.Rows.Count, "F").End(xlUp).Row
If LastRow1 < StartRow1 Then Exit Sub
StartRow2 = 3
LastRow2 = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
If LastRow2 < StartRow2 + 2 Then Exit Sub
Do While StartRow2 < LastRow2
sRoom = GetNum(Sheet2.Range("A" & StartRow2))
i2 = StartRow2 + 2
For i1 = StartRow1 To LastRow1
If .Cells(i1, "F").Value = sRoom Then
.Range(.Cells(i1, "B"), .Cells(i1, "E")).Copy Sheet2.Cells(i2, "B")
i2 = i2 + 1
End If
Next i1
StartRow2 = StartRow2 + JumpNext
Loop
End With
Application.ScreenUpdating = True
End Sub
Private Function GetNum(s As String) As String
Dim retval As String
Dim i As Integer
retval = ""
For i = 1 To Len(s)
Select Case Mid(s, i, 1)
Case "0" To "9"
retval = retval + Mid(s, i, 1)
End Select
Next i
GetNum = retval
End Function
|
|