Forum Discussion

MaksimVaranytsia's avatar
MaksimVaranytsia
Copper Contributor
Jun 20, 2022

User friendly search

Hi, 

I have a excel file with around 40 sheets in it and they have all diffrent structures, i would like to make a search box or button or whatever to search across the whole workbook, and then make the results into a table on a new sheet. I'm looking for a solution for days but had no luck, if someone can help me i'd really appreciate it. 

excel
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jun 20, 2022

    MaksimVaranytsia 

    I would recommend searching on all worksheets to do it best with VBA macros

    ...here is a macro example.

     

    Sub Search()
Dim booFound As Boolean
Dim strInput As String
Dim rngField As Range
Dim wksSheet As Worksheet
strInput = Application.InputBox("Please enter a search term", "Search", "Search term")
If strInput <> "" Then
For Each wksSheet In ActiveWorkbook.Worksheets
For Each rngField In wksSheet.UsedRange
If InStr(UCase(CStr(rngField.Value)), UCase(strInput)) > 0 Then
booFound = True
wksSheet.Activate
rngField.Select
If MsgBox("Find Next?", vbQuestion + vbYesNo) = vbNo Then Exit Sub
End If
Next rngField
Next wksSheet
If booFound = False Then MsgBox "'" & strInput & "' not found!", vbInformation
End If
End Sub

     

     

    ...or

    Activate the relevant workbook.

    Start the search dialog by pressing the key combination Shift + F5 or Ctrl + F.

    Click the "Options" button here.

    Additional search parameters are added to the dialog box.

    From the Search list box, select Workbook.

    Then click the Find All button.

     

     

    I would be happy to know if I could help.

     

    NikolinoDE

    I know I don't know anything (Socrates)

     

Resources