Dec 23 2020 07:46 AM
Is it possible to have the default in Find set to Values instead of Formulas
Dec 23 2020 07:49 AM
If you mean finding exact values that make up the entire cell (without formulas), you can check the Match entire cell contents
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
Thanks
Dec 23 2020 11:13 AM
Can you provide an example of your workbook? In most cases, when using the Find search alone, formulas and values are interchangeable. It is more sensitive when using the Find and Replace option.
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