MsgBox Appears multiple times...reaarange macro so it is only displayed once

Question

I have a script that looks through an entire column and looks for values between 1 - 9, if it encounters a number it throws a message box if it doesnt it currently throws 10 message boxes, I know this is because the second box is still incide the loop.

I have tried placing it out of the loop but with no success, any pointers would be great to get the Else: MsgBox "All locations correctly entered" to display once!

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

score 3 · Accepted Answer · answered Sep 27 '13 at 11:28

You can introduce a boolean type variable which stores true or false. Any boolean variables are false by default, so found by default is equal to false ( you don't have explicitly say found = false but it's optional). So, you only need to change its value to true when rng is not nothing. Added found = true before exiting the loop.

It's logical that something is always false unless its true. So when the values match you toggle the variable state.

At the bottom of the macro code there is an extra line that checks whether the found is false. If it is then one message box will be shown instead of 10+.

Hope this helps

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

MsgBox Appears multiple times...reaarange macro so it is only displayed once

1 Answers1