Search Box In Excel

[flipflop]

Can anyone help?

I'm try to create a macro that searches the whole of a spreadsheet and finds any records that match.....

I've managed to find this...

Sub myfind()
Dim Message, Title, Default, SearchString
Message = "Enter Permit Number/Car Registration Number" ' Set prompt.
Title = "Find Permit/Registration Number" ' Set title.
Default = "" ' 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("A1:IV65536")
Set F = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
If F Is Nothing Then
Else
Location = F.Address
S.Select
Range(Location).Select
Exit For
End If
End With
Next S
End Sub

But what I really want it to do is search and display only the records that match and give an error message if nothing is found, anyone?

[teylyn]

Hi flipflop,

welcome to the forum. Please take a good look at the forum rules, especially with regards to posting code and posting a question in only one forum.

I've added code tags to this post and locked your duplicate post.

I expect you to stick to the rules of this forum from now on.

cheers.

[pike]

Hi flipflop
Really good attempt try playing around with this example

Sub ptest()
searchstring = Sheets("Sheet1").Range("A1")
Set LookInR = Sheets("Sheet2").Range("A1").CurrentRegion
With LookInR
           Set FoundOne = .Find(What:=searchstring, lookat:=xlPart)
        If Not FoundOne Is Nothing Then
            fAddress = FoundOne.Address
            Do
                Range(fAddress).Activate
              Loop While FoundOne.Address <> fAddress
        End If
  
End With
End Sub

[flipflop]

Thanks for your help.

Sorry I'm not very good with things like this trying to learn from scratch...

Is that code to replace all of my code or just part of it?

I'm trying to assign the macro to a button so when you click on it it brings up the search box, you then enter what you want to find and clikc on ok. It will then bring up all records that match.

Sorry again

[pike]

Hi flipflop
try this

Message = "Enter Permit Number/Car Registration Number" ' Set prompt.
Title = "Find Permit/Registration Number" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
SearchString = InputBox(Message, Title, Default)
Set LookInR = Sheets("Sheet1").Range("A1").CurrentRegion
With LookInR
           Set FoundOne = .Find(What:=searchstring, lookat:=xlPart)
        If Not FoundOne Is Nothing Then
            fAddress = FoundOne.Address
            Do
                Range(fAddress).Activate
              Loop While FoundOne.Address <> fAddress
        End If
  
End With
End Sub