VBA code: If not found then Msgbox

[omairhe]

Hey everyone,

How are u? Could you please help me with the code below that searches in column A, a word that begins with bel. All working great, but if it isn't found then pop up a message for 1 second "Entry not found".

Sub search_bel()   
   Dim Fnd As Range
   Set Fnd = Range("A:A").Find("bel*", , xlValues, xlWhole, , , False, False)
   If Not Fnd Is Nothing Then Cells(Fnd.Row, ActiveCell.Column).Activate
End Sub

Thanks and appreciation.

 

[JLGWhiz]

See if this suits your needs.

Sub search_bel()
Dim Fnd As Range, s As Variant
   Set Fnd = Range("A:A").Find("bel*", , xlValues, xlWhole, , , False, False)
   If Not Fnd Is Nothing Then 
        Cells(Fnd.Row, ActiveCell.Column).Activate
   Else
        With ActiveSheet
            Set newWordArt = .Shapes.AddTextEffect(PresetTextEffect:=msoTextEffect1, Text:="ENTRY NOT FOUND", FontName:="Times New Roman", _
                FontSize:=32, FontBold:=False, FontItalic:=False, Left:=ActiveWindow.UsableWidth / 2 - 200, Top:=10)
            s = Timer + 1
                Do While Timer < s
                    DoEvents
                Loop
            newWordArt.Delete
        End With
   End If
End Sub

 

[omairhe]

See if this suits your needs.

Sub search_bel()
Dim Fnd As Range, s As Variant
   Set Fnd = Range("A:A").Find("bel*", , xlValues, xlWhole, , , False, False)
   If Not Fnd Is Nothing Then 
        Cells(Fnd.Row, ActiveCell.Column).Activate
   Else
        With ActiveSheet
            Set newWordArt = .Shapes.AddTextEffect(PresetTextEffect:=msoTextEffect1, Text:="ENTRY NOT FOUND", FontName:="Times New Roman", _
                FontSize:=32, FontBold:=False, FontItalic:=False, Left:=ActiveWindow.UsableWidth / 2 - 200, Top:=10)
            s = Timer + 1
                Do While Timer < s
                    DoEvents
                Loop
            newWordArt.Delete
        End With
   End If
End Sub

Yes. A big thumbs up.