Set Find to look in Values instead of Formulas by default?

[Maury Markowitz]

Is there a setting somewhere that I can use to default Find to search-
in-values instead of search-in-formulas? I would like to be able to
turn this on via code, so that every time the user uses Find it has
defaulted to this setting.

If you're wondering, turning this on also makes Excel ignore data in
hidden columns. I have lots of data in hidden columns that I don't
want the user to Find unless they really go looking for it.

Maury

 

[JLGWhiz]

You can set it when you use the Find function.

Set c = Sheets(1).Range("A1:G500".Find("SomeValue", LookIn:=xlValues)

The above line of code limits the search area to a specific range and tells
it to look in Values for the search criteria "SomeValue"

 

[JLGWhiz]

P.S. There is also a setting in Excel to manually change the LookIn
attribute.

 

[Rick Rothstein]

If macros are enabled, I think you can make it automatically set Find (on
the menu bar) to look in Values using this Worksheet_Open event code...

Private Sub Workbook_Open()
Selection.Find "", LookIn:=xlValues
End Sub

If you are unfamiliar with how to do this, right click the XL icon
immediately to the left of Excel's File menu item, select View Code and then
copy paste the above into the code window that opened up. Now, save the
workbook, close it and then reopen it... the Find "Look in" option should be
defaulted to "Values" now.