User friendly search

%3CLINGO-SUB%20id%3D%22lingo-sub-3527243%22%20slang%3D%22en-US%22%3EUser%20friendly%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3527243%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20excel%20file%20with%20around%2040%20sheets%20in%20it%20and%20they%20have%20all%20diffrent%20structures%2C%20i%20would%20like%20to%20make%20a%20search%20box%20or%20button%20or%20whatever%20to%20search%20across%20the%20whole%20workbook%2C%20and%20then%20make%20the%20results%20into%20a%20table%20on%20a%20new%20sheet.%20I'm%20looking%20for%20a%20solution%20for%20days%20but%20had%20no%20luck%2C%20if%20someone%20can%20help%20me%20i'd%20really%20appreciate%20it.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3527243%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3527937%22%20slang%3D%22en-US%22%3ERe%3A%20User%20friendly%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3527937%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1429822%22%20target%3D%22_blank%22%3E%40MaksimVaranytsia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3EI%20would%20recommend%20searching%20on%20all%20worksheets%20to%20do%20it%20best%20with%20VBA%20macros%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E...here%20is%20a%20macro%20example.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20Search()%0ADim%20booFound%20As%20Boolean%0ADim%20strInput%20As%20String%0ADim%20rngField%20As%20Range%0ADim%20wksSheet%20As%20Worksheet%0AstrInput%20%3D%20Application.InputBox(%22Please%20enter%20a%20search%20term%22%2C%20%22Search%22%2C%20%22Search%20term%22)%0AIf%20strInput%20%26lt%3B%26gt%3B%20%22%22%20Then%0AFor%20Each%20wksSheet%20In%20ActiveWorkbook.Worksheets%0AFor%20Each%20rngField%20In%20wksSheet.UsedRange%0AIf%20InStr(UCase(CStr(rngField.Value))%2C%20UCase(strInput))%20%26gt%3B%200%20Then%0AbooFound%20%3D%20True%0AwksSheet.Activate%0ArngField.Select%0AIf%20MsgBox(%22Find%20Next%3F%22%2C%20vbQuestion%20%2B%20vbYesNo)%20%3D%20vbNo%20Then%20Exit%20Sub%0AEnd%20If%0ANext%20rngField%0ANext%20wksSheet%0AIf%20booFound%20%3D%20False%20Then%20MsgBox%20%22'%22%20%26amp%3B%20strInput%20%26amp%3B%20%22'%20not%20found!%22%2C%20vbInformation%0AEnd%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E...or%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3EActivate%20the%20relevant%20workbook.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3EStart%20the%20search%20dialog%20by%20pressing%20the%20key%20combination%20Shift%20%2B%20F5%20or%20Ctrl%20%2B%20F.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3EClick%20the%20%22Options%22%20button%20here.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3EAdditional%20search%20parameters%20are%20added%20to%20the%20dialog%20box.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3EFrom%20the%20Search%20list%20box%2C%20%3CSTRONG%3Eselect%20Workbook%3C%2FSTRONG%3E.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3EThen%20click%20the%20Find%20All%20button.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fmeet-niko-chatzoudis-excel-forum-contributor%2Fba-p%2F2941385%22%20target%3D%22_blank%22%3ENikolinoDE%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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. 

1 Reply

@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)