'数据源工作簿中的行列 Const START_ROW_SOURCE As Integer = 2 Const ID_SOURCE As String = "B" Const RESULT_SOURCE As String = "C" '抽奖结果工作簿中的行列 Const ID_RESULT As String = "B" Const FIRST_CELL_RESULT As String = "B5" Const START_ROW_RESULT As Integer = 5 '数据源最大行 Private maxRow_Source As Integer '开始抽奖 Private Sub cmdDraw_Click() On Error GoTo ErrorHandler: '取得当前的中奖等级 Dim rewardLevel As String rewardLevel = txtLevel.Text '取得得奖的人数 Dim rewardCount As Integer If (Trim(txtCount) <> "") Then rewardCount = CInt(txtCount.Text) Else MsgBox ("请输入中奖人数!") End If maxRow_Source = getMaxRow(shtDataSource) '清除当前结果 Dim maxRow_result As Integer maxRow_result = getMaxRow(shtDrawResult) If (maxRow_result > START_ROW_RESULT) Then shtDrawResult.Range(FIRST_CELL_RESULT, shtDrawResult.Cells.SpecialCells(xlCellTypeLastCell)).Value = "" End If '已经抽出的数量 Dim drewCount As Integer drewCount = 0 Dim curResultRow As Integer curResultRow = START_ROW_RESULT Dim randomRow As Integer Dim currentID As String Dim currentRewardStatus As String '循环抽出指定数量的中奖凭证 While (drewCount < rewardCount) '取得一个随机数 randomRow = (maxRow_Source - START_ROW_SOURCE + 1) * Rnd + START_ROW_SOURCE '该行数据即为被抽中 currentID = shtDataSource.Range(ID_SOURCE & CStr(randomRow)).Cells.Value If Trim(currentID) <> "" Then '检查该凭证是否已经被抽过奖 currentRewardStatus = shtDataSource.Range(RESULT_SOURCE & CStr(randomRow)).Cells.Value If Trim(currentRewardStatus) = "" Then '复制到抽奖结果中来 shtDataSource.Range(ID_SOURCE & CStr(randomRow)).Copy shtDrawResult.Range(ID_RESULT & CStr(curResultRow)).PasteSpecial (xlPasteAll) '设置数据源中的中奖栏位 shtDataSource.Range(RESULT_SOURCE & CStr(randomRow)).Cells.Value = rewardLevel curResultRow = curResultRow + 1 End If End If drewCount = drewCount + 1 Wend GoTo ExitHandler ErrorHandler: MsgBox ("出现问题!") Exit Sub ExitHandler: MsgBox ("完成!") End Sub '取得工作簿的最大行 Function getMaxRow(sht As Worksheet) As Integer Dim lastCell As Range Set lastCell = sht.Cells.SpecialCells(xlCellTypeLastCell) getMaxRow = lastCell.Row End Function