Search bar that searches through multiple columns in another worksheet

[Elaina]

I have a large inventory database (almost 8,000 rows by 38 columns) generated by querying many different spreadsheets. The queries are merged into one giant table that I would like to search from. Each row contains many columns of information about the piece of equipment that someone might need to know. However, there could be duplicate equipment from the different spreadsheets I'm querying, but one was described in one way in a specific column, and the other person described it slightly differently but with similar key words in a different column.

I've found VBA code for a search bar that looks for the results on a different sheet and I've found VBA code using option boxes to search by different column titles, but I'm not experienced enough with code language to be able to combine the concepts myself.

• I'm looking for a search bar which will search through a large amount of data I have on a separate sheet.
• I would also like to be able to do a search on different columns' data, or even multiple columns at a time would be ideal. Maybe a combo box with columns titles to select which rows to search in or just a general search for any word or number matches in the entire data range could work as well. (It could serve my purposes well even if I could just search in one desired column at a time, but it would function better the more columns I'm able to search at once.)
• Then I would like the rows of data that match to be listed below the search bar.
• Also I want there to be an asterisks in the search so that if I search 1234, then 12345 will show up as well as AB1234.
• Bonus: Being able to search for more than one search item at a time. (For instance, "1200HP motor", motor would be found in one column and 1200HP would be found in another.)

Good result: I'm looking for "motors" within the "equipment" column on the separate sheet titled "All" and all of the rows with motors in the equipment column would show up below.

Better result: I'm looking for the part number "22367A-1000" in the columns "Part # OEM" and "Part # Vendor" on the separate sheet "All" and all of the rows that contain those numbers and letters in a row would show up below my search.


Is this possible? Or is there a better way to get the result I'm looking for? Let me know if I need to provide more information.

 

[Elaina]

The formatting issue I seemed to be having is no longer happening.

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim foundVal As Range
    Dim sAddr As String
    ActiveSheet.UsedRange.Offset(4, 0).ClearContents
    With Sheets("All").UsedRange
        Set foundVal = .Find(Target, LookIn:=xlValues, lookat:=xlPart)
        If Not foundVal Is Nothing Then
            sAddr = foundVal.Address
            Do
                foundVal.EntireRow.Copy Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                Set foundVal = .FindNext(foundVal)
            Loop While foundVal.Address <> sAddr
            sAddr = ""
        End If
    End With
    Set foundVal = Nothing
    Application.ScreenUpdating = True
End Sub

 

[Elaina]

Yes. Thank you so much.

 

[mumps]

You are very welcome.