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

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.

Roy Sprunger wrote:

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

--

Dave Peterson

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" wrote in message
...
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.

Roy Sprunger wrote:

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

--

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 wrote:

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" wrote in message
...
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.

Roy Sprunger wrote:

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

--

Dave Peterson

--

Dave Peterson

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" wrote in message
...
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 wrote:

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" wrote in message
...
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.

Roy Sprunger wrote:

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

--

Dave Peterson

--

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.


Tyro wrote:

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" wrote in message
...
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 wrote:

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" wrote in message
...
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.

Roy Sprunger wrote:

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

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" wrote:

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.


Tyro wrote:

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" wrote in message
...
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 wrote:

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" wrote in message
...
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.

Roy Sprunger wrote:

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

--

Dave Peterson

--

Dave Peterson

--

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.



Roy Sprunger wrote:

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" wrote:

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.


Tyro wrote:

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" wrote in message
...
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 wrote:

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" wrote in message
...
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.

Roy Sprunger wrote:

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson