Find and search by column

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 


0
bgill36 (7)
5/10/2005 10:27:14 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
515 Views

Similar Articles

[PageSpeed] 54

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

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

-- 

Dave Peterson
0
ec357201 (5290)
5/10/2005 11:45:02 PM
This is just great! Thank you Dave.

Brian

"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message 
news:4281477E.76D5BEE0@netscapeXSPAM.com...
> 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
>
> Brian wrote:
>>
>> 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
>
> -- 
>
> Dave Peterson 


0
bgill36 (7)
5/11/2005 12:38:48 AM
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

"Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht 
news:4281477E.76D5BEE0@netscapeXSPAM.com...
> 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
>
> Brian wrote:
>>
>> 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
>
> -- 
>
> Dave Peterson 


0
j.sons (216)
5/11/2005 11:13:39 AM
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!)



Jack Sons wrote:
> 
> 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
> 
> "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
> news:4281477E.76D5BEE0@netscapeXSPAM.com...
> > 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
> >
> > Brian wrote:
> >>
> >> 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
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
ec357201 (5290)
5/11/2005 11:18:33 PM
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.

"Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht 
news:428292C9.4B4336BC@netscapeXSPAM.com...
> 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!)
>
>
>
> Jack Sons wrote:
>>
>> 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
>>
>> "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
>> news:4281477E.76D5BEE0@netscapeXSPAM.com...
>> > 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
>> >
>> > Brian wrote:
>> >>
>> >> 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
>> >
>> > --
>> >
>> > Dave Peterson
>
> -- 
>
> Dave Peterson 


0
j.sons (216)
5/12/2005 1:50:35 PM
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 Sons wrote:
> 
> 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.
> 
> "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
> news:428292C9.4B4336BC@netscapeXSPAM.com...
> > 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!)
> >
> >
> >
> > Jack Sons wrote:
> >>
> >> 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
> >>
> >> "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
> >> news:4281477E.76D5BEE0@netscapeXSPAM.com...
> >> > 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
> >> >
> >> > Brian wrote:
> >> >>
> >> >> 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
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
ec357201 (5290)
5/12/2005 3:51:15 PM
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.

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.



"Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht 
news:42837B73.A7FD7F99@netscapeXSPAM.com...
> 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 Sons wrote:
>>
>> 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.
>>
>> "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
>> news:428292C9.4B4336BC@netscapeXSPAM.com...
>> > 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!)
>> >
>> >
>> >
>> > Jack Sons wrote:
>> >>
>> >> 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
>> >>
>> >> "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
>> >> news:4281477E.76D5BEE0@netscapeXSPAM.com...
>> >> > 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
>> >> >
>> >> > Brian wrote:
>> >> >>
>> >> >> 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
>> >> >
>> >> > --
>> >> >
>> >> > Dave Peterson
>> >
>> > --
>> >
>> > Dave Peterson
>
> -- 
>
> Dave Peterson 


0
j.sons (216)
5/12/2005 9:54:01 PM
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 Sons wrote:
> 
> 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.
> 
> 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.
> 
> "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
> news:42837B73.A7FD7F99@netscapeXSPAM.com...
> > 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 Sons wrote:
> >>
> >> 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.
> >>
> >> "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
> >> news:428292C9.4B4336BC@netscapeXSPAM.com...
> >> > 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!)
> >> >
> >> >
> >> >
> >> > Jack Sons wrote:
> >> >>
> >> >> 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
> >> >>
> >> >> "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
> >> >> news:4281477E.76D5BEE0@netscapeXSPAM.com...
> >> >> > 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
> >> >> >
> >> >> > Brian wrote:
> >> >> >>
> >> >> >> 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
> >> >> >
> >> >> > --
> >> >> >
> >> >> > Dave Peterson
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
ec357201 (5290)
5/12/2005 11:35:02 PM
Reply:

Similar Artilces:

How can I search for a ? in Publisher?
I did a Search & Replace and somehow ? marks were placed throughout my 32-page document. I want to be sure I found them all, but when I search for ?, it stops at every letter. Appreciate some advice. Thanks. It doesn't work for me either. It doesn't work in 2010 as well. You can checkmark *Match whole words only* Publisher will look between each word rather than each letter. Not much help when you are dealing with 32 pages. -- Mary Sauer http://msauer.mvps.org/ "guymaine" <guymaine@discussions.microsoft.com> wrote in message news:B89D6FF6-B7...

MATCHING COLUMNS
I HAVE A QUESTION ABOUT MATCHING COLUMNS IN EXCEL. FOR INSTANCE I HAVE TWO COLUMNS ONE IN A AND ONE IN B WITH ALL DIFFERENT VALUES. HOW DO I MATCH COLUMN A WITH COLUMN B? AN EXAMPLE IS BELOW A B 000824108498310 000824108378310 000825252525151 000824108378310 002020204582810 000824108448310 020202222225550 000824108460310 None of your values matches any value from other column! Regards, -- AP PS: ALL CAPS means yelling: please avoid! <HOOSICK@NYCAP.RR.COM> a...

pivot table grand totals not showing for all columns...
i have a pivot table that shows the grand total for some columns, but not for others... there are no differences in the data layout, yet this happens? any clues as to why? and how I can get the grand totals to show for all fields. thanx in the PivotTable floating toolbar, click PviotTable, then Table Options, select 'Grand totals for columns' option. ...

Find both matched and unmatched entries
Hi, I have two tables that I wish to find: 1) find matched data - copy that into a separate table 2) find unmatched data - copy that data into a separate table Can I achieve the above results with one query? I have three fields in each table that I would like to compare against: Client Data Status Any assistance with this would be greatly appreciated... Cheers, GLT Hi Yes you can use an append query to do this quite simple BUT you would end up with the same data in 2 tables which be a mistake. Maybe you could post some more details of what you are trying to do (the end result) a...

how do i search for a specific name in an excel file
i have a large file, (49,000+ names) is it possible to search for a specific name somehow without scrolling up and down the list repeatedly looking for it? Hit CTRL+F to open the find window, and in Look in: select Values. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6261 View this thread: http://www.excelforum.com/showthread.php?threadid=496546 Did you try Edit > Find ? "cljenkins" wrote: > i have a large file, (49,000+ names) is it...

how to find all matches in an array
I have a task list that in one column lists the person primarily responsible for it and in the 2nd column is the back up person. The 3rd column is the task. On another sheet I want to put in someones name and bring up all tasks they are responsible for and all the tasks they are backup for. What is an easy way to do this. Easiest way IMHO would be to use advanced filter http://www.contextures.com/xladvfilter01.html -- Regards, Peo Sjoblom (No private emails please) "Greg" <Greg@discussions.microsoft.com> wrote in message news:FA4CB596-6451-4569-81AF-24EC18C50180...

Find the number of years between two dates
I have aroster of players with thier date of births. I would like to automatically add their ages in another column Try this Formula, assuming that the dob is in cell A1 =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" regards Mukesh "duke" wrote: > I have aroster of players with thier date of births. I would like to > automatically add their ages in another column On Fri, 9 Sep 2005 19:47:02 -0700, "duke" <duke@disc...

Outlook 2007 Instant Search not working.
I installed Outlook 2007 on Windows XP SP2. The instant search does not work and desktop search can find everything except email messages. I have rebuilt the index and uninstalled and reinstalled Window Desktop Search 3.0. Any other ideas on how to fix this? I fixed mine by doing two things, not scientific, doing only one may have worked. I unchecked Outlook Express. (Maybe it only likes one email client) (It search OE fine by the way) I ran the repair option for Office 2007 in add remove programs. (I installed search after Outlook prompted me on its first use, maybe Search should be...

How do I arrange entries in a column alphabetically
I have typed a list of entries in a column in Excel 2003 and now I want to list them alphabetically. Any ideas how I do this? Select the column, go to Data > Sort, and sort ascending. HTH Jason Atlanta, GA >-----Original Message----- >I have typed a list of entries in a column in Excel 2003 and now I want to >list them alphabetically. Any ideas how I do this? >. > ...

grouping cells to sort by only one column
Hi, New to this so please bear with me. I have a spread sheet where I am listing names in column A and othe info relating to that name in columns B and C in the two rows below th name. I then start a different name in the next row down with the info in t two rows below that, and so forth. I want to be able to add more names in the future and be able to sor all of this by column A only. The problem I am having is the info i the other columns moves. I need it to somehow attatch and stay put wit it's corisponding name without moving. In other words how do I make everything from A1 to ...

Imported text file w/o unique identifiers, find duplicates
Imported a txt file w/o unique identifiers and have run a query to find possible duplicate records based on 3 seperate fields. How can I either update those fields that may be in error in the original table or append a new record w/o the duplicate data and delete the original record? In the following I need to determine which records to keep, which to delete and/or which to update. End Date Total Sheets Time Operator Name 3/3/2007 14 0:00 00094123 B D MCNARA 3/3/2007 14 0:00 00095681 NAME UNKNOWN 3/17/2007 84 0:01 00091234 C D S 3/17/2007 84 0:01 000...

Excel's column width format box
Does anyone know why I am unable to enter a number into Excel's column width box, yet using the mouse, I am able to adjust the width. When opening the text box, I can delete the number that appears, but am unable to even reenter that one after it is deleted. I must tell you that I'm a new user who's working through numerous tutorials. Thanks, Hi do you get an error message or what happens exactly -- Regards Frank Kabel Frankfurt, Germany rly2rys wrote: > Does anyone know why I am unable to enter a number into Excel's > column width box, yet using the mouse, I am ...

Need RULE to search for Text in Body with varying # of spaces
Hi there, I have Outlook 2002 and have been using the outlook rule condition: "With specific words in the body" However the "Specific words" I'm looking for might contain varying number of spaces: EX: "some text: some more text" "some text: some more text" Is there a wildcard or something to match multiple spaces? --Thanks very much, Kenneth Kenneth Parker <kenneth.parker@sas.com> wrote: > I have Outlook 2002 and have been using the outlook rule condition: > "With specific words in the body" > > However...

Report Columns
I am trying to add columns to my report but am having some difficulty. The report is basically a form letter. It is grouped on the employee’s unique ID. In that group header is the letter, Dear so and so etc. It is the details portion that I am trying to get into two columns. Is there something I am missing on how to do this. I would rather no put a sub report in to accomplish this. Thank you Jason, Well, the subform would be an easy way to go about that. However, I would think you could use the ID group header to display the first part of the letter, the detail section to disp...

Searching trick?
I am trying to search for a string from an imported file. The lead character in the string is the * character. Of course, if I do a search in Excel like '*', it finds everything. How can I get it to treat * like a character and not a wild card?? Use this in the Find dialog Bill ~* -- Regards Ron de Bruin http://www.rondebruin.nl "Bill" <anonymous@discussions.microsoft.com> wrote in message news:e49e01c43c53$d48f4dd0$a501280a@phx.gbl... > I am trying to search for a string from an imported file. > The lead character in the string is the * character. Of &g...

Need to find the table
I do get some odd requests for great plains. My latest requires me to track down a table. The one i need is for one of the forms that shows in the smart list. I need the one under account transactions and is called Output VAT Nominal. I need to know what table/s supply this form. Even better would be if it was possible to access the hard coding of this form. I looked for it and found nothing. We run great plains version 8 should it help. well, thankyou all kindly in advance. Tools>Resource Descriptions is always a good place to start. "Saltious" wrote: > I do get som...

Help: MAPI can't find PSTPRX.DLL
Hi there Can anyone suggest how I can make outlook 2002 work properly. Everytime I press send/receive it says MAPI can not locate PSTPRX.DLL. I have done search and it is not on my computer. Please can anyone assist? just trying out 1st time >-----Original Message----- >Hi there > >Can anyone suggest how I can make outlook 2002 work >properly. Everytime I press send/receive it says MAPI can >not locate PSTPRX.DLL. I have done search and it is not >on my computer. Please can anyone assist? >. > See if this info helps: http://support.microsoft.com/default.asp...

Cannot find DLL when starting debugging
Sorry for asking this here, but vstudio.general seems to be rather sleepy... Anyway: I have an MFC app which utilizes a 3rd party DLL which is located in its own place on the harddrive. I've tried to put the path to the directory where the DLL can be found in the list under 'Tools', 'Options', 'VC++ Directories', 'Executable Files', but it still can't seem to find the DLL when I try to debug my app. I know I could just copy the DLL to my Debug directory, but ahh that's really clumsy. Isn't there an other way ? :-| Bendik "Bendik E...

search help
I have a form which contain 2 unbound text boxes and a subform. 1st text box is where I input client's ID and 2nd textbox is for client's name. I have set the child and master field for subform to clients ID on subform and 1st testbox on main form. This works fine if I only have to search by client's ID. My problem is how do I make the subform to requery if ,say the client forget his ID # or search using ID produce no result, and I have to search by name using 2nd textbox as a search string. I tried changing the master and child field link using code but that didn't work...li...

Copy column headings AND formula totals to blank spreadsheet
Hi Everyone! I apologize that this was likely asked before but could someone show me how I can copy my 'column headings, column widths and totals formula' to a blank spreadsheet tab? I have set up budget spreadsheet where I input my receipts to keep track of monthly expenditures. Could someone explain how to copy the column headings, the column widths, AND the formulas of each column to a blank spreadsheet tab so each month will contain the 'same headings, same column widths and the same formula for each column'? Thanks so much for any help!!! katy Assuming you alr...

finding 600 emails in a column
Hi! Thank you for taking the time to read this. I run an internet retail business. We advertise on search engine large and small. I have to make a decision on whether or not one of our smaller ad site is paying off. Basically, the ad site supplies us with a list o POTENTIAL CUSTOMER emails. We send out monthly promotional emails t them. Now i need to track these emails to see if any of them have becom ESTABLISHED CUSTOMERS. So far, I fed information from our main data base of ESTABLISHE CUSTOMERS into EXCELL so all the ESTABLISHED CUSTOMER emails are in on column. But i don't kn...

Column that needs separation
I am very new with excel and I am putting together a worksheet that has a column consisting of two primary names. I would like to format the entire column with the result being both names having their own color throughout the column. Alternatively, I could go to each row and highlight the name but there are about 1000 rows. Thanks, Aaron Say column A has entries like: James Ravenswood Run this small macro: Sub Colorizer() Dim A As Range, r As Range Set A = Intersect(ActiveSheet.UsedRange, Range("A:A")) For Each r In A v = r.Value s = Split(v, ...

squashed columns
Apologies if this has been asked before. One of my users occasionally has a problem where the columns in her spreadsheet appear all "squashed up" when she opens the file. She is using Office 97 SR-2. Any ideas why this is happening? ~Paul. Did she or any one else save the file with a zoom factor (far) less then 100% ? -- Regards, Auk Ales * Please reply to this newsgroup only * * I will not react on unsolicited e-mails * "Paul Birney" <pbirney@eircom.ie> wrote in message news:uqstZ%23QAEHA.2484@TK2MSFTNGP12.phx.gbl... > Apologies if this has been asked ...

Adding columns given a match
Hello, I have 2 tables with different data, except for a member ID column. Table A has 38,000 records with about 35 columns. Table B has 3,000 records, with about 9 columns. I need to read table B, scan table A with the member ID from table B and add the 3 last cells from table B to table A for that member. I have a general idea how to do it, but that's about it. I need help. Any suggestions will be greatly appreciated. Thanks much in advance, Cefoxtrot Simplest technique is INDEX/MATCH. This link will probably be broken by the forum, but remove the breaks to make one long line ag...

Find newest price
I have a spreadsheet with three column, like Part.no Date Price 1 02/02/05 10 2 02/02/05 15 3 03/11/05 30 4 04/11/05 40 1 05/05/05 12 3 05/10/05 25 4 06/06/05 50 1 06/09/05 20 How do I find the newest price for each part.no? I tried with =SUMPRODUCT((A1:A50=D1)*(B1:B50=MAX(B1:B50))*(C1:C50)) but it only works for the part-no that has been changed on the last date. D1 is where i type the part.no which prize I need to find. Jan First, save your data and use this only...