Searching a Workbook by Default

Something I would find useful is the ability to set Within to "Workbook" but for it to default back to "Sheet" immediately on completion of the search rather than persist for the whole session. Maybe you'd like to put this on your agenda for future tips. Keep up the good work.

To my opinion the title should have been:
"Can we Always Search a Workbook by Default" ?
----------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL

Hi,
not working. Still selecting worksheet instead of workbook when I run the macro. What to do? Office 2013 on Win 8.1

@ Jimmy
These should get you started
http://www.eileenslounge.com/viewtopic.php?f=50&t=15555
https://excelribbon.tips.net/T008148_Understanding_Macros.html
https://excelribbon.tips.net/T005683_Recording_a_Macro.html
https://excelribbon.tips.net/T009753_Editing_Macros.html
Alan

Hi
A question was asked in the pre XL 2007 version of this article ( https://excel.tips.net/T003170_Searching_a_Workbook_by_Default.html )
I am rather curious myself as to the answer, considering that both articles are titled “Searching a Workbook by Default”.
‘_ - The code is great to set different parameters to the dialogue box. However I have not found a way to set the Within to workbook.
‘_- Can anyone tell me ( us ) how to do this with the code.
Thanks
Alan

How to use this vb code in excel... A step by step guide would be appreciable.

This works great for bringing the window up, but is there a way for a macro to automatically close this window down as well? I tried changing the .Execute to .Close but that failed. Any ideas?

Kudos to Walter K for his suggestion. I've wanted to select within=workbook by default for years, now I have a method that makes that unimportant.

Highlight all the Tabs and then do a FIND? Right-button click, select all.

This method is BETTER than within=workbook because you can choose a custom set of tabs to search within.

Good thing, since the mistaken LookAt:=xlWhole is a non-starter. No range parameter can be specified.

Like it! But is there a way to read the parameters so I can save them and then restore them after running my macro?

Thank you Walter K. I am trying it again. I have all the tabs selected. It still says "Sheet" in the "Within:" box. But yes, it is finding on all the sheets in spite of it saying "Sheet." I must have searched for something that was only contained in the first sheet on my previous try.

@Scott Renz
It looks like you may be only selecting the first Tab or Worksheet in the Workbook. Make sure you have all the Tabs selected that you want to search. To do this either hold down the Ctrl key for each Tab to search or select the first Tab then hold down the Shift key and select the final Tab in your Workbook. Should work, good luck!

@gerdami Many Thanks!

Just a warning about the persistent state of the Within box.

When you select a range and do a Find & Replace (F&R),
(1) if Within Sheet is selected, only the cells of the range are affected by the F&R
(2) if Within Workbook is selected, ALL Workbook cells are affected by the F&R

@S Barker:
The list of IDs is available at http://support.microsoft.com/kb/213552

Hi Walter,

It still shows "Sheet" for me when I select all the tabs and do Ctrl+F and it only searches on the first sheet.

@Walter. Wow, that may seem simple/obvious, but I never knew that functionality existed. Good suggestion.

Maybe most users or readers herein want a more simplisistic way to FIND in a workbook or you have 10-20+ Tabs or something. But instead of a Macro, isn't it just as easy to highlight all the Tabs and then do a FIND? Doesn't require you to change to Worksheet to Workbook or anything, just type in what you want to FIND or REPLACE and you're good to go. Am I missing something?

Hi Tony Davis,
xlWhole means the whole "Cell" must match the looked for text. Whereas, xlPart means only a part of the "Cell" can match the looked for text and it would be considered a match. xlWhole does not refer to the whole workbook. It refers to the whole cell.

I wrote a macro to search a workbook for a value in both formulas and cell text. It is stored in my PERSONAL.XLS file so it is available at any time.

It adds a sheet called SearchResults at the beginning of the file. Any matches are listed by sheet and cell reference. Each time you run the macro, it deletes the sheet (if it exists) and creates a new one for the next set of results. All sheets, including hidden ones, are searched.

Here is the code, which I hope you find is useful.

=====================================

Sub ListSearchValues()
' macro written by Dave Kerr, January 2014
' objective: search for a string in either formulae or cell values
' output the results in a new sheet in the same workbook

Dim Srch As String ' search string
Dim NS As Integer ' number of sheets in workbook
Dim j As Integer ' loop counter
Dim wsThis As Worksheet
Dim i As Integer
Dim k As Integer

Srch = inputbox("Search value", "Find a value")
If Len(Srch) = 0 Then ' cancel pressed or no input
Exit Sub
End If

Application.ScreenUpdating = False

On Error Resume Next ' bypass an error if it arises
Sheets("SearchResults").Delete ' delete a search results sheet if it exists
On Error GoTo 0

Sheets(1).Select ' go to the first sheet
Sheets.Add ' add a new sheet for the list of links
ActiveSheet.Name = "SearchResults"
Set wsThis = ActiveSheet
Range("A1").Value = "Formula Search"
Range("C1").Value = Srch
Range("E1").Value = "Cell Value Search"
i = 2
k = 2

NS = Sheets.Count

For j = 2 To NS
Sheets(j).Activate

On Error Resume Next

Range("A1:" & ActiveCell.SpecialCells(xlLastCell).Address).Select
For Each Cell In Selection
Application.StatusBar = ActiveSheet.Name & " Cell " & Cell.Address
If Cell.HasFormula Then
If InStr(UCase(Cell.Formula), UCase(Srch)) > 0 Then ' if the cell's formula contains the input string
wsThis.Range("A" & i).Value = ActiveSheet.Name
wsThis.Range("B" & i).Value = Cell.Address
wsThis.Range("C" & i).Value = "'" & Cell.Formula
i = i + 1
End If
Else
If InStr(UCase(Cell.Value), UCase(Srch)) > 0 Then ' if the cell's value contains the input string
wsThis.Range("D" & k).Value = ActiveSheet.Name
wsThis.Range("E" & k).Value = Cell.Address
wsThis.Range("F" & k).Value = Cell.Value
k = k + 1
End If
End If
Next ' next cell
Range("A1").Select
Next ' next sheet

If i = 2 And k = 2 Then
wsThis.Activate
wsThis.Range("C3").Value = "No match found for " & Srch
End If

Application.ScreenUpdating = True
Application.StatusBar = ""
wsThis.Activate
Range("A1").Select
Columns("A:F").EntireColumn.AutoFit

End Sub

Al,

I'm not sure I understand your last sentence.
Are you saying you still don't know how to use the macro provided on the microsoft website to search the whole spreadsheet?
If so, today's tip tells you to set "LookAt" equal to "xlWhole" to look at the whole workbook.
I think you just need to specify xlWhole as the Lookat parameter.

It is very useful to be able to replace the defaults of the search function. I connected Find2 with the ctrl+f shortcut and now can get customized default parameters. (I set the sort order to columns and the look in to values.) The valid parameters can be found at http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.find(v=office.14).ASPX

However, the parameters as posted do not look in the entire workbook, but instead require the entire cell to match the find string.

I would like to know how to set to search entire workbook, but the parameters as documented in the above address do not list a way to search the entire workbook.

So where are the IDs listed for each dialog/menu? And is there an equivalent list for Outlook and Word? I specifically would like the Outlook one, as one of the defaults drives me crazy.