Find and search by column

B

Brian

When I execute Edit then Find, Excel begins to search by row. Can anyone
suggest how:
(a) to directly change this default or
(b) to incorporate a few lines of VBA codes
so as to make it search by column without having to select Options first,
and change the search command to "By Column"?
Thank you for your help.

Brian
 
D

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:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=True

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
 
B

Brian

This is just great! Thank you Dave.

Brian

Dave Peterson said:
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:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=True

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
Click to expand...
 
J

Jack Sons

Dave,

More or less related:

Every time I open a new wokbook zeros will be visibly represented, so I
always have to uncheck that option in tools>options> etc.

In C:\program files\microsoft office\office\xlstart I put a "map1.xls" of
which all sheets have the zeros option unchecked. When I start Excel that
map.xls pops up, as expected, but for every extra sheet in that workbook and
any new workbook I open the zeros option has to be unchecked again.

I simply want every new sheet of every new (and preferably also excisting)
workbook to have the zeros option unchecked by default.

What should I do to accomplish that?

Jack Sons
The Netherlands
 
D

Dave Peterson

Personally, I'd use a macro to toggle that setting and run it on demand.

But you could use a workbook that looks when a window is activated and turns off
the display.

If you put this code behind ThisWorkbook and save the workbook in your XLStart
folder:

Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub xlApp_SheetActivate(ByVal Sh As Object)
ActiveWindow.DisplayZeros = False
End Sub
Private Sub xlApp_WorkbookActivate(ByVal Wb As Workbook)
ActiveWindow.DisplayZeros = False
End Sub

Each time you activate a different sheet or a different workbook, that setting
will be turned off.

=======
But seriously, I don't think I've ever turned off zeros in real life. I like
seeing them. (Yes, it would cause changes to workbooks that I shared with
people who like them off!)

I'd just add a macro like this:

Option Explicit
Sub ToggleZeros()
With ActiveWindow
.DisplayZeros = Not .DisplayZeros
End With
End Sub

(I don't really like stuff that looks too much like magic!)
 
J

Jack Sons

Dave,

I did as you said, it works fine. Now map1.xls has your code in the
thisworkbook module, should I save it "hidden" in xlstart, like
personal.xls?

Strange thng (to me) is that when I start Excel it opens with map1.xls. When
I open a new workbook it is called (by default) map1 (no extension) and
subsequent started new worbooks are called map2, map 3 etc. (Map is the
Dutch word for book or workbook) Is that te way it should be? I expected
that the workbook opened after map1.xls would be automaticaly named map2.xls
etc.

Jack.
 
D

Dave Peterson

You could actually just include that code in your personal.xls file itself.
(You may have to a little tweaking to merge it into your existing workbook_open
event--but very small tweaks.

I didn't realize that "map" was the equivalent of "book". If you have map1.xls
in your xlstart folder, it'll load when you start excel. I have a workbook
named book.xlt in my XLStart. It's the template that is used for any new
workbooks I start. But I don't have book1.xls in my XLStart folder.

Unless you're doing something pretty weird, the only time you'll see the .xls
extension is when the file has already been saved once. It's standard for new
workbooks to not have any extension.

Jack said:
Dave,

I did as you said, it works fine. Now map1.xls has your code in the
thisworkbook module, should I save it "hidden" in xlstart, like
personal.xls?

Strange thng (to me) is that when I start Excel it opens with map1.xls. When
I open a new workbook it is called (by default) map1 (no extension) and
subsequent started new worbooks are called map2, map 3 etc. (Map is the
Dutch word for book or workbook) Is that te way it should be? I expected
that the workbook opened after map1.xls would be automaticaly named map2.xls
etc.

Jack.
Click to expand...
 
J

Jack Sons

Dave,

Maybe I got it finally? I renamed map1.xls to map1.xlt, opened it, hid it
and then saved it again to xlstart (Same procedure that goes for
personal.xls, isn't it?). Is your book.xlt also hidden?

My personal.xls seems not to have a ThisWokbook module, I possibly deleted
it long agong but that is only a guess. Should personal.xls have a
ThisWorkbook module?
(You may have to a little tweaking to merge it into your existing
workbook_open
event--but very small tweaks.
Click to expand...

I do not have any workbook_open events in my personal.xls. Should I have
them? What is the use if personal.xls is a hidden workbook that only
functions in the background, personal.xls is still a bit of a mistery for
me.

Jack.
 
D

Dave Peterson

Book.xlt doesn't doesn't need to be hidden. In fact, if it's hidden, then any
new workbook based on that template will be hidden, too. And that will make it
very difficult to work on it <vbg>.

Check again in your personal.xls workbook. It should be there--you can't delete
it. But you could have renamed it in the properties window--but you'd have to
go out of your way pretty far to do that.

And you shouldn't have a problem with personal.xls after you add that code.
(But like all workbooks (files in general), it should be backed up to a nice
safe location.)

Since you don't have any (well, that you found), merging should consist of just
copying and pasting.



Jack said:
Dave,

Maybe I got it finally? I renamed map1.xls to map1.xlt, opened it, hid it
and then saved it again to xlstart (Same procedure that goes for
personal.xls, isn't it?). Is your book.xlt also hidden?

My personal.xls seems not to have a ThisWokbook module, I possibly deleted
it long agong but that is only a guess. Should personal.xls have a
ThisWorkbook module?
(You may have to a little tweaking to merge it into your existing
workbook_open
event--but very small tweaks.
Click to expand...

I do not have any workbook_open events in my personal.xls. Should I have
them? What is the use if personal.xls is a hidden workbook that only
functions in the background, personal.xls is still a bit of a mistery for
me.

Jack.
Click to expand...
 
Top