Dec 23 2020 07:46 AM
Dec 23 2020 09:31 AM
Yes but I don't usually look for cells containing part of a cell. I would search dining if I wanted to see all the dining tables, chairs, and benches in this file. I want to know if it is possible for the default in the "Look In" box to be Values
Dec 23 2020 12:11 PM
Nope, there is no such setting. You may use macro on workbook run to change the settings, first I found is quite old Change Excel Find & Replace Options Defaults (excelforum.com), but you may google for more samples if such variant works with you.
Dec 23 2020 12:57 PM
You could use a macro that invokes a built-in dialog and set that argument to find values. The downside is it doesn't have all of the options (find formats and scope) and it doesn't appear to limit the search to the range you've selected before invoking the dialog - it appears to search the entire sheet regardless.
Sub ShowFindDialog() '// arg1 What text to find '// arg2 LookIn formulas (1) or values (2) or comments (3) '// arg3 LookAt whole cells (1 or xlWhole) or part (2 or xlPart) '// arg4 SearchOrder xlByRows (1) or xlByColumns (2) '// arg5 SearchDirection xlNext (1) or xlPrevious (2) '// arg6 MatchCase True or False '// arg7 MatchByte True or False Application.Dialogs(xlDialogFormulaFind).Show arg2:=2 End Sub