Search box

[Jimmasterton]

Hello everyone
I got such great help last time I posted a question and I hoped you could help me again.

I have got a workbook with 366 sheets in it, sheet 1 being the cover sheet.

On the cover sheet (sheet1) I have merged 3 horizontal cell (D14,E14,F14) to make a box for searchable words and put a button with a magnifying glass icon to the right of it.

What i would Like to be able to happen is, I type a word into the search box and click the magnifying glass search button and it jumps to the cell of the first instance of this word being found. There might be more than one time the same word is mentioned in all of the sheets, so, when the first search is done I would like a message box to appear saying

is this what you were looking for ?

If yes the selection should remain on this cell
If no the search should continue to the next instance of this word being mentioned eventually with a message box saying
no searches found
and when you press ok it returns to the cover sheet.

I would like a relaxed search if possible, so if the user searches for Smith and the cell contains David Smith it will still find it.

I have seen some other posts for search boxes but none like this, can you help me please?

 

[JoeMo]

The easy way to do this is to use Excel's Find tool: Home Tab>Editing>Find & Select. But if you want to use a macro, assign this one to your button on Sheet1 and try it (lightly tested). Note the comment in red about case sensitivity.

Sub SearchBox()
Dim searchString As Variant, Fnd As Range, Sht As Worksheet, Adr As String, Hits As Long
If Sheets("Sheet1").Range("D14").Value = "" Then
    MsgBox "Search Box is Empty - exiting search"
    Exit Sub
Else
    searchString = Sheets("Sheet1").Range("D14").Value
    For Each Sht In ThisWorkbook.Worksheets
        If Sht.Name <> "Sheet1" Then
            Set Fnd = Sht.Cells.Find(searchString, , xlValues, xlPart, , , True)  'change True to false for case insensitive search
            If Not Fnd Is Nothing Then
                Hits = Hits + 1
                Adr = Fnd.Address
                Application.Goto Fnd
Again:
                If MsgBox("Is this what you're looking for?", vbYesNo) = vbNo Then
                    Do
                        Set Fnd = Sht.Cells.FindNext(Fnd)
                        If Fnd Is Nothing Then Exit Do
                        If Fnd.Address = Adr Then Exit Do
                        Hits = Hits + 1
                        Application.Goto Fnd
                        GoTo Again
                    Loop
                Else
                    MsgBox "Glad you found what you want - Goodbye!"
                    Exit Sub
                End If
            End If
        End If
    Next Sht
    MsgBox "Except for the " & Hits & " instances of " & searchString & " already offered to you, could not find " & searchString & " in any worksheet"
End If
End Sub

 

[Jimmasterton]

JoeMo, thanks very much for your help, I’ll try this first thing on Monday.

Thanks again

Can you or the other experts recommend any mr excel books for someone who wants to learn Vba but doesn’t really know where to start?

 

[JoeMo]

I recommend you PM hiker95 who keeps an extensive VBA reference list of books, websites and ....

 

[Fluff]

Hiker95's list can be found here https://www.mrexcel.com/forum/excel...e-template-excel-spreadsheet.html#post4972842

 

[Jimmasterton]

Thank you Joe and Fluff
Joe, your search script runs amazingly, it’must be great to be able to write this kind of thing based on a quite vague description like mine. Anyway, thanks again.

 

[JoeMo]

Thank you Joe and Fluff
Joe, your search script runs amazingly, it’must be great to be able to write this kind of thing based on a quite vague description like mine. Anyway, thanks again.

You are welcome - thanks for the reply ... and keep those vague descriptions coming in.