Search form

[elmo2000]

Hi There

This is my first post (And VBA newby)

I found this code (did a little modifying) in this forum for a search in excel, works fine but can you help me on this one:
How can show a message to the user when nothing is found?

I added MsgBox "Niets gevonden" (Nothing found in English) , but this show on every search. Where do I place within this code?

[COLOR=#000000][FONT=-webkit-standard]Sub myfind()[/FONT][/COLOR][COLOR=#000000][FONT=-webkit-standard]  Dim Message, Title, Default, SearchString[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  Message = "Zoeken op gedetineerde!" ' Set prompt.[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  Title = "Toets een naam in" ' Set title.[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  Default = "B.Oef" ' Set default.[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  ' Display message, title, and default value.[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  SearchString = InputBox(Message, Title, Default)[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]
[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  'SearchString = "Rob"[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  Set S = Sheets.Application[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  For Each S In Application.Sheets[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  With S.Range("B1:B400")[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  Set F = .Find(SearchString, MatchCase:=False, LookAt:=xlPart, [/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]LookIn:=xlValues)[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]   If F Is Nothing Then[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  Else[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  Location = F.Address[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  S.Select[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  Range(Location).Select[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  Exit For[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  End If[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  End With[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  Next S[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]  End Sub[/FONT][/COLOR]

 

[JackDanIce]

Corrected for rng error and to only test sheet Verdeling, try:

Sub myFind()

    Dim LR      As Long
    Dim rng     As Range
    Dim strSrch As String
    
    strSrch = InputBox("Zoeken op gedetineerde!", "Toets een naam in", "B.Oef")
    If Len(strSrch) = 0 Then Exit Sub
            
    With Sheets("Verdeling")
        .Select
        LR = .Cells(.Rows.count, 2).End(xlUp).row
        On Error Resume Next
        Set rng = .Cells(1, 2).Resize(LR).find(what:=strSrch, lookat:=xlPart, LookIn:=xlValues, MatchCase:=False)
        On Error GoTo 0
        If Not rng Is Nothing Then
            rng.Select
            Set rng = Nothing
            Exit For
        Else
            MsgBox "Niets gevonden: " & .Name, vbExclamation, "Niets gevonden"
        End If
    End With
    
End Sub

 

[elmo2000]

I found a solution, Tx all!