Change default search to Value, not formula?

  • Thread starter Thread starter Auto
  • Start date Start date
A

Auto

Every time I start a word search, the default searches for fomulas instead
of value. So everytime I have to open up the option button and change it
to value instead of formula.
How do I change that default setting?

lbbss
 
Saved from a previous post:

Excel tries to help by remembering the last settings you used--except for the
first search in that session.

You can use that to your advantage.

You could make a dummy workbook and put it in your xlStart folder. Have a
macro in that workbook that does a find (and sets all the stuff the way you
like). Then closes and gets out of the way.


Option Explicit
Sub auto_open()

Worksheets("sheet1").Cells.Find What:="", After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False

ThisWorkbook.Close savechanges:=False

End Sub

The workbook opens, does a find (to fix your settings) and then closes to get
out of the way.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But if you do an Edit|Find and change anything, then those changed settings will
be remembered.
 
So it sounds like my excel is not working as it should. I only once did a
find formula, since then I have always done a search value. But it does
not seam to remember to save my last search. Also, I wish it would keep
the search entire work book setting, but it always defaults to search in
sheet.
 
Each time you open excel, it goes back to its default--to look in formulas.

If you do a find and change to values, it'll stay values until you change it--or
close excel and reopen it.

I don't know a way to change the "within" option. There's nothing in code you
can do--maybe play around with Sendkeys and tabs, but I wouldn't do that (and
haven't).
 
So it sounds like my excel is not working as it should. I only once did a
find formula, since then I have always done a search value. But it does
not seam to remember to save my last search. Also, I wish it would keep
the search entire work book setting, but it always defaults to search in
sheet.
 
I don't have any more guesses.
So it sounds like my excel is not working as it should. I only once did a
find formula, since then I have always done a search value. But it does
not seam to remember to save my last search. Also, I wish it would keep
the search entire work book setting, but it always defaults to search in
sheet.
Click to expand...
 
Dear Dave;

I tried your macro, but it did not change the search parameter to 'Value' and it did not close. I copied your macro and pasted it directly into a macro in a blank Excel file, then saved that file to my xlStartup folder. The file opens at startup, but does not operate as advertised.

My questions: Do I need to have content in the startup Excel file? Or, are there parameters in your macro that need to be changed for my individual use? Or, do you think I've done something wrong incorprating the macro into my startup file?

(I am not greatly familiar with macros; thanks for the link to 'getting started' website.)

Pat Sanford



Dave Peterson wrote:

Re: Change default search to Value, not formula?
18-Jun-07

Saved from a previous post

Excel tries to help by remembering the last settings you used--except for th
first search in that session

You can use that to your advantage

You could make a dummy workbook and put it in your xlStart folder. Have
macro in that workbook that does a find (and sets all the stuff the way yo
like). Then closes and gets out of the way

Option Explici
Sub auto_open(

Worksheets("sheet1").Cells.Find What:="", After:=ActiveCell,
LookIn:=xlValues,
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False

ThisWorkbook.Close savechanges:=Fals

End Su

The workbook opens, does a find (to fix your settings) and then closes to ge
out of the way

If you're new to macros, you may want to read David McRitchie's intro at
http://www.mvps.org/dmcritchie/excel/getstarted.ht

But if you do an Edit|Find and change anything, then those changed settings wil
be remembered

Auto wrote

--

Dave Peterson

EggHeadCafe - Software Developer Portal of Choice
Silverlight IsolatedStorage Compressed Object Cache
http://www.eggheadcafe.com/tutorial...a-5b86b0c676c9/silverlight-isolatedstora.aspx
 
Dave;
I reviewed my steps in creating the macro and found my mistake. Thanks again for that handy 'getting started with macros' link; it helped me to troubleshoot my steps. Basically, I had put the code into sheet 1 instead of into module 1.
It works like a charm and I couldn't be happier (unless MS took Office 2007 off the market and created Office 2009 that continued on from the user-friendly (if not as pretty) and efficient 2003 products).
Thanks a million!!!



Pat Sanford wrote:

Excel Search default to Value
08-Oct-09

Dear Dave;

I tried your macro, but it did not change the search parameter to 'Value' and it did not close. I copied your macro and pasted it directly into a macro in a blank Excel file, then saved that file to my xlStartup folder. The file opens at startup, but does not operate as advertised.

My questions: Do I need to have content in the startup Excel file? Or, are there parameters in your macro that need to be changed for my individual use? Or, do you think I've done something wrong incorprating the macro into my startup file?

(I am not greatly familiar with macros; thanks for the link to 'getting started' website.)

Pat Sanford

EggHeadCafe - Software Developer Portal of Choice
Using the MS Text Driver to read Delimited Files
http://www.eggheadcafe.com/tutorial...c2-3895d1a2df28/using-the-ms-text-driver.aspx
 
Back
Top