Change Default Setting-Find & Replace-How?

[Roy Sprunger]

How do I change the default behavior of the Find & Replace dialog box to
Search by Columns versus Rows?

 

[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()

'change the settings the way you like
Worksheets("sheet1").Cells.Find What:="", After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
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.

 

[Tyro]

If I select a range of 5 columns and 20 rows there are 100 cells. If I
select a range of 20 columns and 5 rows there are 100 cells. Either way the
search has to search 100 cells. No documentation on Excel I have ever seen,
has told me what difference search by rows or columns makes. Do you know?

Tyro

 

[Dave Peterson]

If you do a replace all, I don't think it would make a difference.

But if you're hitting the find next button, then it would make a difference.

 

[Tyro]

Even find next has to look through the 100 cells. If all the occurrences are
in one row and I select search by row, Find Next does not know that there
are no other occurrences until all 100 cells have been searched row by row
Or, am I missing something?.

Tyro

 

[Dave Peterson]

If all the occurrences are in a single row or a single column, there's no
difference. But the selection pattern of find next will be different when the
searched for values are not in that single column/row.

Even find next has to look through the 100 cells. If all the occurrences are
in one row and I select search by row, Find Next does not know that there
are no other occurrences until all 100 cells have been searched row by row
Or, am I missing something?.

Tyro

 

[Roy Sprunger]

Gentlemen, you are missing the point.

Excel may search every cell in a worksheet but it appears to stop on the
first hit.

In my experience, if I have 35,000+ rows by 50 columns and I know the
keyword is in column A, specifying to search by columns returns a hit faster
than it does searching by rows.

Be that as it may, it appears there is no way to change the program defaults
from within the program. It will require an external influence.

I am finding Excel 2003 has a great deal less flexibility and allowable user
customization than Word 2003 without resorting to Macros or Visual Basic.


Roy

 

[Dave Peterson]

I don't think I missed the point.

But if you know the value you're searching for is in column A, then select
column A before you begin the search. The search will be limited to the cells
in the selection.

Gentlemen, you are missing the point.

Excel may search every cell in a worksheet but it appears to stop on the
first hit.

In my experience, if I have 35,000+ rows by 50 columns and I know the
keyword is in column A, specifying to search by columns returns a hit faster
than it does searching by rows.

Be that as it may, it appears there is no way to change the program defaults
from within the program. It will require an external influence.

I am finding Excel 2003 has a great deal less flexibility and allowable user
customization than Word 2003 without resorting to Macros or Visual Basic.

Roy