【實(shí)例05-隨機(jī)提取指定行】Excel表格VBA編程實(shí)例 代碼分享
Public addrow As Long
'提取并刪除
Public rndrow2 As Long
Public minnum2 As Long
Public maxnum2 As Long
Private Sub CommandButton清空結(jié)果_Click()
? ? addrow = 0
? ? With ThisWorkbook.Worksheets("提取結(jié)果")
? ? ? ? .UsedRange.ClearFormats
? ? ? ? .UsedRange.ClearContents
? ? End With
End Sub
Private Sub CommandButton提取1_Click()
With ThisWorkbook.Worksheets("操作界面")
'判斷輸入?yún)?shù)，并賦值到變量
Dim minnum As Long
Dim maxnum As Long
? ? ?If .Cells(2, "C").Value <> "" And .Cells(5, "C").Value <> "" Then
? ? ?minnum = .Cells(2, "C").Value
? ? ?maxnum = .Cells(5, "C").Value
? ? ?Else
? ? ?MsgBox "參數(shù)未輸入完全"
? ? ?Exit Sub
? ? ?End If
'生成隨機(jī)提取的行
Dim rndrow As Long
rndrow = 生成隨機(jī)整數(shù)(minnum, maxnum)
If rndrow > 0 Then? '大于0就提取行
? ? addrow = addrow + 1 '提取數(shù)據(jù)放置的行位置
? ? ThisWorkbook.Worksheets("原數(shù)據(jù)").Rows(rndrow).Copy
? ? ThisWorkbook.Worksheets("提取結(jié)果").Rows(addrow).PasteSpecial Paste:=xlPasteAll
End If
End With
'ThisWorkbook.Worksheets("提取結(jié)果").Activate
'ThisWorkbook.Worksheets("提取結(jié)果").Cells(addrow, 1).Select
End Sub
Public Function 生成隨機(jī)整數(shù)(ByVal lrnd As Long, ByVal urnd As Long) As Long
'--------------------------------隨機(jī)整數(shù)
生成隨機(jī)整數(shù) = Application.WorksheetFunction.RandBetween(lrnd, urnd)
End Function
Private Sub CommandButton提取2_Click()
With ThisWorkbook.Worksheets("操作界面")
'判斷輸入?yún)?shù)，并賦值到變量
'判斷是否是首次提取
If addrow = 0 Then
? ? ?If .Cells(2, "C").Value <> "" And .Cells(5, "C").Value <> "" Then
? ? ?minnum2 = .Cells(2, "C").Value
? ? ?maxnum2 = .Cells(5, "C").Value
? ? ?Else
? ? ?MsgBox "參數(shù)未輸入完全"
? ? ?Exit Sub
? ? ?End If
'生成隨機(jī)提取的行
rndrow2 = 生成隨機(jī)整數(shù)(minnum2, maxnum2)
End If
If rndrow2 > 0 Then? '大于0就提取行
? ? addrow = addrow + 1 '提取數(shù)據(jù)放置的行位置
? ? ThisWorkbook.Worksheets("原數(shù)據(jù)").Rows(rndrow2).Copy
? ? ThisWorkbook.Worksheets("提取結(jié)果").Rows(addrow).PasteSpecial Paste:=xlPasteAll
? ? ThisWorkbook.Worksheets("原數(shù)據(jù)").Rows(rndrow2).Delete
? ? maxnum2 = maxnum2 - 1
? ? rndrow2 = 生成隨機(jī)整數(shù)(minnum2, maxnum2)
End If
End With
'ThisWorkbook.Worksheets("提取結(jié)果").Activate
'ThisWorkbook.Worksheets("提取結(jié)果").Cells(addrow, 1).Select
End Sub
