Search form

E

elmo2000

New Member
Joined
Oct 2, 2017
Messages
15
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?

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]
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Try this
Code: 
Sub myfind()
Dim Message [COLOR=#0000ff]As String[/COLOR], Title [COLOR=#0000ff]As String[/COLOR], Default[COLOR=#0000ff] As String[/COLOR], SearchString [COLOR=#0000ff]As String[/COLOR]
  
  Message = "Zoeken op gedetineerde!" ' Set prompt.
  Title = "Toets een naam in" ' Set title.
  Default = "B.Oef" ' Set default.
  ' Display message, title, and default value.
  SearchString = InputBox(Message, Title, Default)


  'SearchString = "Rob"
  Set s = Sheets.Application
  For Each s In Application.Sheets
    With s.Range("B1:B400")
        Set F = .Find(SearchString, MatchCase:=False, LookAt:=xlPart, _
              LookIn:=xlValues)
         If F Is Nothing Then
            [COLOR=#0000ff]MsgBox "Nothing Found"[/COLOR]
        Else
            Location = F.Address
            s.Select
            Range(Location).Select
            Exit For
        End If
    End With
  Next s
  
End Sub
Modifications in blue
 
Upvote 0
Welcome to the board.

Try:
Code: 
Sub myFind()

    Dim w       As Long
    Dim LR      As Long
    Dim rng     As String
    Dim strSrch As String
    
    strSrch = InputBox("Zoeken op gedetineerde!", "Toets een naam in", "B.Oef")
    If len(strSrch) = 0 Then Exit Sub

    Application.ScreenUpdating = False
            
    For w = 1 To Worksheets.count
        With Sheets(w)
            .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
    Next w
    
    Application.ScreenUpdating = True
    
End Sub
 
Last edited:
Upvote 0
Hello,

Try placing it here

Code: 
Sub myfind()  
  Dim Message, Title, Default, SearchString
  Message = "Zoeken op gedetineerde!" ' Set prompt.
  Title = "Toets een naam in" ' Set title.
  Default = "B.Oef" ' Set default.
  ' Display message, title, and default value.
  SearchString = InputBox(Message, Title, Default)




  'SearchString = "Rob"
  Set S = Sheets.Application
  For Each S In Application.Sheets
  With S.Range("B1:B400")
  Set F = .Find(SearchString, MatchCase:=False, LookAt:=xlPart, LookIn:=xlValues)
  If F Is Nothing Then
  [COLOR=#ff0000]MsgBox "Niets gevonden"[/COLOR]
  Else
  Location = F.Address
  S.Select
  Range(Location).Select
  Exit For
  End If
  End With
  Next S
  End Sub
 
Upvote 0
Do you need the user to be informed of every sheet where the value is not found unless value is found?

Your code is checking each sheet and stopping when the value is found (if found) - if there's only one instance of the value, you could notify the user at the very end if either the value exists (then select that sheet and the cell) or does not, rather than be notified of every single sheet where the value isn't found...
 
Upvote 0
Fluff said:
Try this
Code: 
Sub myfind()
Dim Message [COLOR=#0000ff]As String[/COLOR], Title [COLOR=#0000ff]As String[/COLOR], Default[COLOR=#0000ff] As String[/COLOR], SearchString [COLOR=#0000ff]As String[/COLOR]
  
  Message = "Zoeken op gedetineerde!" ' Set prompt.
  Title = "Toets een naam in" ' Set title.
  Default = "B.Oef" ' Set default.
  ' Display message, title, and default value.
  SearchString = InputBox(Message, Title, Default)


  'SearchString = "Rob"
  Set s = Sheets.Application
  For Each s In Application.Sheets
    With s.Range("B1:B400")
        Set F = .Find(SearchString, MatchCase:=False, LookAt:=xlPart, _
              LookIn:=xlValues)
         If F Is Nothing Then
            [COLOR=#0000ff]MsgBox "Nothing Found"[/COLOR]
        Else
            Location = F.Address
            s.Select
            Range(Location).Select
            Exit For
        End If
    End With
  Next s
  
End Sub
Modifications in blue
Click to expand...

Hi,

This one doen'st work properly. It's gives a message on every search. And I have to click three times on no results (If there no results, if there are results just click once)
 
Upvote 0
JackDanIce said:
Welcome to the board.

Try:
Code: 
Sub myFind()

    Dim w       As Long
    Dim LR      As Long
    Dim rng     As String
    Dim strSrch As String
    
    strSrch = InputBox("Zoeken op gedetineerde!", "Toets een naam in", "B.Oef")
    If len(strSrch) = 0 Then Exit Sub

    Application.ScreenUpdating = False
            
    For w = 1 To Worksheets.count
        With Sheets(w)
            .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
    Next w
    
    Application.ScreenUpdating = True
    
End Sub
Click to expand...

Hi Jack.

This one gives a error (Bold) on: Set rng = .Cells(1, 2).Resize(LR).find(what:=strSrch, lookat:=xlPart, LookIn:=xlValues, MatchCase:=False)
 
Upvote 0
Nine Zero said:
Hello,

Try placing it here

Code: 
Sub myfind()  
  Dim Message, Title, Default, SearchString
  Message = "Zoeken op gedetineerde!" ' Set prompt.
  Title = "Toets een naam in" ' Set title.
  Default = "B.Oef" ' Set default.
  ' Display message, title, and default value.
  SearchString = InputBox(Message, Title, Default)




  'SearchString = "Rob"
  Set S = Sheets.Application
  For Each S In Application.Sheets
  With S.Range("B1:B400")
  Set F = .Find(SearchString, MatchCase:=False, LookAt:=xlPart, LookIn:=xlValues)
  If F Is Nothing Then
  [COLOR=#ff0000]MsgBox "Niets gevonden"[/COLOR]
  Else
  Location = F.Address
  S.Select
  Range(Location).Select
  Exit For
  End If
  End With
  Next S
  End Sub
Click to expand...


Hi Nine Zero,


Hi,

This one also doesn't work properly. It's gives a message on every search. And I have to click three times on no results (If there no results, if there are results just click once)
 
Upvote 0
JackDanIce said:
Do you need the user to be informed of every sheet where the value is not found unless value is found?

Your code is checking each sheet and stopping when the value is found (if found) - if there's only one instance of the value, you could notify the user at the very end if either the value exists (then select that sheet and the cell) or does not, rather than be notified of every single sheet where the value isn't found...
Click to expand...

Hi Jack,

Actually there has to by a search in the sheet "Verdeling" (B2:B400) not in every sheet. So basically only "no results"when nothing found on sheet "Verdeling" in that specific column. If the query is found, just select that cell. ( And if possible hightlight it with a color)

Any help on this?

Tx.
 
Upvote 0
Found this one

Sub Find_First()
Dim FindString As String
Dim Rng As Range
FindString = InputBox("Wie zoek je?")
If Trim(FindString) <> "" Then
With Sheets("Verdeling").Range("B2:B400")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Niets gevonden!"
End If
End With
End If
End Sub
Click to expand...

That will do what i want, accept show multiple results if there are same values.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Macro to find Duplicates and highlight them across a workbook (9sheets)
Replies
0
Views
493
BlackTiger
B
B
  • Question Question
increment by input message box and interpolate between removed data
Replies
5
Views
534
Brandon M
B
T
  • Question Question
Speed up a slow Macro?
Replies
4
Views
366
pjmorris
P
T
  • Question Question
VBA Search box macro - small correction needed
Replies
2
Views
429
ttratl
T
C
  • Question Question
Error 3709 the search key was not found. Help with this message
Replies
5
Views
2K
xenou
xenou

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top