Search box and search button help

[JKolya]

Hello! I am new to the forum - have lurked around before, but just joined and need help with a macro for a search box. Attached is a sample of my database and an example (pulled from another thread on here) that I wish to copy. I have searched these forums and played with code that others recommended for other people, but have not been successful in adapting them to my needs. Any and all help is appreciated.

In my database I would like a search box with a command button similar to the one in the attached "example database to copy." However, I do not want the search option or the results to be on a separate page. I would simply like them to be displayed on the same page underneath the same headings with all other data removed. Then, when you clear the search box all data returns.

Users need to be able to search the entire database (ie, not limited to one column, but whatever word they search for could come from any column). Results should appear when any "part" of the search is found. Last, this database is updated and added to monthly, and it would be great if the "ranges" covered allowed me to add additional data without messing up the code/search function.

Thank you for any help.

Jason

[Carlos Toloza]

Hello JKolya,

I think the example I am sending you will meet most of your requirements.



Best Regards,
Toloza.

[JKolya]

Toloza,

Thank you for the reply and effort, however; my database is the other one - "Sample Database for search box option." The one titled "Example database to copy," is just to show a sample of the type of search function I am looking for.

I tried to play with the one you sent, but the search does not seem to reply with the correct results. For example, searching "belt," brings three replies, but two of them do not contain the "belt." I am not sure why. Then when I delete the word, the data does not come back. Not sure what to do.

J

[AlphaFrog]

Here are two macros you could assign to buttons. One to search for the prompted search term and the other to reset and show all the data again.

Sub Find_All()
    Dim Found As Range, rngAll As Range, FirstFound As String, strSearchTerm As String
    
    strSearchTerm = Application.InputBox("Enter the search term.", "Search Term", Type:=2)
    If strSearchTerm = "False" Then Exit Sub    'User canceled
    
    Rows.Hidden = False
    Set Found = Cells.Find(What:=strSearchTerm, _
                           LookIn:=xlValues, _
                           LookAt:=xlPart, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=False)
                           
    If Not Found Is Nothing Then
        FirstFound = Found.Address
        Set rngAll = Found
        Do
            Set Found = Cells.FindNext(After:=Found)
            If Found.Address <> FirstFound Then Set rngAll = Union(rngAll, Found)
        Loop While Found.Address <> FirstFound
        
        Application.ScreenUpdating = False
            rngAll.Select
            Set rngAll = Union(rngAll, Rows("1:4")) 'Header rows
            ActiveSheet.UsedRange.Rows.Hidden = True
            rngAll.EntireRow.Hidden = False
        Application.ScreenUpdating = True
        
    Else
        MsgBox "No match found for '" & strSearchTerm & "'.", vbExclamation, "No Match Found"
    End If
    
End Sub
    
Sub Show_All()
    Rows.Hidden = False
End Sub

[JOHN H. DAVIS]

Maybe:

Sub JKolya()
For Each cell In Sheets("Data").Range("B2:B" & Sheets("Data").UsedRange.Rows.count + 1)
    If cell.Value Like "*" & Sheets("Search").Range("B2").Value & "*" Then
        cell.Offset(, -1).Copy Sheets("Search").Range("A" & Rows.count).End(3)(2)
        cell.Copy Sheets("Search").Range("B" & Rows.count).End(3)(2)
    End If
Next cell
End Sub

[JKolya]

AlphaFrog,

That worked perfectly. Thank you so much for taking the time.

J