Find & Select

%3CLINGO-SUB%20id%3D%22lingo-sub-2012641%22%20slang%3D%22en-US%22%3EFind%20%26amp%3B%20Select%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2012641%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20it%20possible%20to%20have%20the%20default%20in%20Find%20set%20to%20Values%20instead%20of%20Formulas%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2012641%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2012660%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20%26amp%3B%20Select%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2012660%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F910408%22%20target%3D%22_blank%22%3E%40Gord0462%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20mean%20finding%20exact%20values%20that%20make%20up%20the%20entire%20cell%20(without%20formulas)%2C%20you%20can%20check%20the%20Match%20entire%20cell%20contents%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22adversi_0-1608738544361.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242528i41E8E51CE2771DA7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22adversi_0-1608738544361.png%22%20alt%3D%22adversi_0-1608738544361.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2012884%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20%26amp%3B%20Select%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2012884%22%20slang%3D%22en-US%22%3E%3CP%3EYes%20but%20I%20don't%20usually%20look%20for%20cells%20containing%20part%20of%20a%20cell.%20I%20would%20search%20dining%20if%20I%20wanted%20to%20see%20all%20the%20dining%20tables%2C%20chairs%2C%20and%20benches%20in%20this%20file.%20I%20want%20to%20know%20if%20it%20is%20possible%20for%20the%20default%20in%20the%20%22Look%20In%22%20box%20to%20be%20Values%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F867265%22%20target%3D%22_blank%22%3E%40adversi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2013065%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20%26amp%3B%20Select%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2013065%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F910408%22%20target%3D%22_blank%22%3E%40Gord0462%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20provide%20an%20example%20of%20your%20workbook%3F%20In%20most%20cases%2C%20when%20using%20the%20Find%20search%20alone%2C%20formulas%20and%20values%20are%20interchangeable.%20It%20is%20more%20sensitive%20when%20using%20the%20Find%20and%20Replace%20option.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Is it possible to have the default in Find set to Values instead of Formulas

5 Replies

@Gord0462 

If you mean finding exact values that make up the entire cell (without formulas), you can check the Match entire cell contents

adversi_0-1608738544361.png

 

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

@adversi 

@Gord0462 

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.

@Gord0462 

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.

@Gord0462 

 

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