vba - MsgBox出现多次...重新排列宏所以只显示一次

Question

我有一个脚本，它查看整个列并查找 1 - 9 之间的值，如果它遇到一个数字，它会抛出一个消息框，如果没有，它当前会抛出 10 个消息框，我知道这是因为第二个框仍然包含循环。

我曾尝试将其置于循环之外，但没有成功，任何指针都可以很好地让 Else: MsgBox "All locations correctly entered"显示一次!

Sub Scoring()
Dim FindString As String
Dim rng As Range
Dim startVal As Integer, endVal As Integer

startVal = 1
endVal = 9

For i = startVal To endVal
    FindString = CStr(i)
    With Sheets("Scoring").Range("S:S")
        Set rng = .Find(What:=FindString, _
                After:=.Cells(.Cells.Count), _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)
        If Not rng Is Nothing Then
            MsgBox "There are one or more risks that do not contain the minimum       information required for import, please ammend these and try again.", True
            Exit For
        Else: MsgBox "All locations correctly entered"

       End If
    End With
 Next i

End Sub

Answer 1

您可以引入一个存储true 或false 的 bool 类型变量。默认情况下，任何 bool 变量都是 false，因此 found 默认等于 false(您没有明确说明 found = false 但它是可选的)。所以，当rng不是空的时候，你只需要将它的值改为true。在退出循环之前添加了 found = true。

合乎逻辑的是，除非某件事是真的，否则它总是假的。因此，当值匹配时，您会切换变量状态。

在宏代码的底部有一行检查 found 是否为 false。如果是，则将显示一个消息框，而不是 10 个以上。

希望对你有帮助

Sub Scoring()
Dim FindString As String
Dim rng As Range
Dim startVal As Integer, endVal As Integer, i As Long

startVal = 1
endVal = 9

Dim found As Boolean

For i = startVal To endVal
    FindString = CStr(i)
    With Sheets("Scoring").Range("S:S")
        Set rng = .Find(What:=FindString, _
                After:=.Cells(.Cells.Count), _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)
        If Not rng Is Nothing Then
            MsgBox "There are one or more risks that do not contain the minimum       information required for import, please ammend these and try again.", True
            found = True
            Exit For
       End If
    End With
 Next i

If Not found Then MsgBox "All locations correctly entered"

End Sub