☰

当选定的单元格不等于 5 且不连续时，VBA 发出警告

tag-icon • tag-icon microsoft-excel vba macros
当选定的单元格不等于 5 且不连续时，VBA 发出警告

我的 Vb 宏执行以下操作：我选择 5 个连续的单元格，运行我的宏，然后我得到 5 个介于 1 和 50 之间的静态随机数；它运行完美；当选定的单元格不连续且不等于 5 时，我需要发出警告。选定的单元格必须是 5 个并且连续。不知道我该如何以及在哪里放置警告。谢谢！

Sub loto()
    from = 1
    until = 50
 Selection.ClearContents
    For Each cell In Selection.Cells
        If WorksheetFunction.CountA(Selection) = (until - from + 1) Then Exit For
        Do
            rndNumber = Int((until - from + 1) * Rnd() + from)
        Loop Until Selection.Cells.Find(rndNumber, LookIn:=xlValues, lookat:=xlWhole) Is Nothing
        cell.Value = rndNumber
    Next
End Sub

答案1

回答你的直接问题：

If Selection.Areas.Count > 1 Or Selection.Cells.Count <> 5 Then

更彻底地，我们可以稍微清理一下您的代码，删除多余的行并添加一些额外的检查。例如，如果用户选择一个形状然后运行代码，您的代码就会出错。

Sub loto()

    'Declarations
    Const minValue As Integer = 1
    Const maxValue As Integer = 50
    Const cellCount As Integer = 5
    Dim rng As Range
    Dim cell As Range
    Dim errorMessage As String
    errorMessage = "You must select " & cellCount & " contiguous cells!"

    'Check that the selection is a range of cells and not some object
    On Error Resume Next
        Set rng = Selection
    On Error GoTo 0
    If rng Is Nothing Then
        MsgBox errorMessage, vbExclamation, "Error"
        Exit Sub
    End If

    'Check that five contiguous cells are selected
    If rng.Areas.Count > 1 Or rng.Cells.Count <> cellCount Then
        MsgBox errorMessage, vbExclamation, "Error"
        Exit Sub
    End If

    'Loop through each and add values
    rng.ClearContents
    For Each cell In rng.Cells
        Do
            cell.Value = Int((maxValue - minValue + 1) * Rnd() + minValue)
        Loop Until WorksheetFunction.CountIf(rng, cell.Value) = 1
    Next

End Sub

相关内容