Find feature defaults to look in formulas

[dford]

In 2003 the find feature defaults to look in formulas. Is there a way to have
it default to look in values?

 

[dford]

Can someone help here?

 

[Nick Hodge]

Not that I know of

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
www.nickhodge.co.uk

 

[Dave Peterson]

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:=xlFormulas, _
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.

 

[Nick Hodge]

Dave

That appears to be the only way (Which IMO is a little overkill). I was
hoping book.xlt may work, but tested that and nada :-((

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
www.nickhodge.co.uk

 

[Dave Peterson]

Maybe a better home would be personal.xl* (without the .close statement).

I don't think I'd want something like this in my workbook template anyhow.

 

[dford]

Is there a way to create a macro within the worksheet I am using to open the
find window, change to workbook, change to values, and leave the window open?

 

[Dave Peterson]

You could fiddle with the workbook setting by using sendkeys. But I don't think
there's any other way to get to that setting.

But you could modify that other macro to look more like this:

Option Explicit
Sub auto_open()

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

Application.Dialogs(xlDialogFormulaFind).Show
ThisWorkbook.Close savechanges:=False
End Sub

 

[dford]

I still do not have this figured out yet. When I run the code below I get the
error - Runtine error 1004
Method 'worksheets' of object_global' failed.

 

[Dave Peterson]

I still do not have this figured out yet. When I run the code below I get the
error - Runtine error 1004
Method 'worksheets' of object_global' failed.

 

[dford]

This code is all yellow in debuger

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

 

[Dave Peterson]

Try:

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

This code is all yellow in debuger

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

 

[Dave Peterson]

In fact, use this instead:

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

 

[dford]

I get a
Compile error:
Syntax error:

Starting with the line below.
with ThisWorkbook.Worksheets("sheet1").
The above test was red

Maybe I'm entering the code improperly. I inserted a new module, named the
macro ChangeFindDefault, inserted your code between the name and End Sub.

In fact, use this instead:

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

 

[Dave Peterson]

Drop that final period.

with ThisWorkbook.Worksheets("sheet1").
becomes
with ThisWorkbook.Worksheets("sheet1")

Make sure that there's a worksheet named Sheet1 in that workbook, too.

I get a
Compile error:
Syntax error:

Starting with the line below.
with ThisWorkbook.Worksheets("sheet1").
The above test was red

Maybe I'm entering the code improperly. I inserted a new module, named the
macro ChangeFindDefault, inserted your code between the name and End Sub.

 

[dford]

The code seems to run without errors but the find feature still defaults to
Sheet & Formulas instead of Workbook & values.

Drop that final period.

with ThisWorkbook.Worksheets("sheet1").
becomes
with ThisWorkbook.Worksheets("sheet1")

Make sure that there's a worksheet named Sheet1 in that workbook, too.

 

[Dave Peterson]

Excel's VBA doesn't allow you to change the workbook/sheet option.

I don't have a guess why formulas would still be showing up for you. That part
worked fine for me.

The code seems to run without errors but the find feature still defaults to
Sheet & Formulas instead of Workbook & values.

 

[dford]

You are correct. Formulas did change to values. I missed that. If it is not
possible to change from sheet to workbook, I will have to just click a couple
more times. if you or anyone else comes up with a solution in the future,
please let me know. Thanks for your help.