VBA MSGBOX For each instance error

[jamesmev]

I currently have the following vba

 Dim SrchRng As Range, cel As Range
    Set SrchRng = Sheets("Brand").Range("J:J")
    For Each cel In SrchRng
        If InStr(1, cel.Value, "Material") > 0 Then
            MsgBox "There is a error which needs to be keyed at lower level. Please filter sheet."
        End If

However, if there are multiple instances of "Material" in J it produces a pop up for each instance. I Just need it to pop up once. Not for each instance.
Any help?

 

[6StringJazzer]

When posting code please post all the code. I know it seems like you have shown only the part of code necessary, but I am going to give you a solution that could possibly break your code depending on what the rest of your code looks like. Also I suggest an error message that tells the user what the error actually is.

Replace all code shown with this. Also remove the "Next" statement, which you did not show.

    If Not Sheets("Brand").Range("J:J").Find(what:="Material", lookAt:=xlPart, lookin:=xlValues) Is Nothing Then
            MsgBox """Material"" found in column J. This is an error which needs to be keyed at lower level. Please filter sheet."
   End If

 

[mikerickson]

Try

Dim blnIsError as Boolean
'...
blnIsError = False
For Each cel In SrchRng
       blnIsError = blnIsError Or (InStr(1, cel.Value, "Material") > 0)
Next cel

If blnIsError Then MsgBox "There is a error which needs to be keyed at lower level. Please filter sheet."