Hi All,
I am looking for a way to program the Find and Replace window. What I want to do
is set up a hot key to call the window with the contents of the selected cell in
the "Find What?" space. (Ordinarily I would just record a macro & alter what I
got but that recording feature does not seem to record anything when the Find
and Replace sub-window is used.)
Thank You,
-plh


--
Where are we going and why am I in this HAND BASKET??

Try


Code:
--------------------
Application.Dialogs(xlDialogFormulaFind).Show
--------------------


--
royUK

Hope that helps.

RoyUK
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24999

You can use Application.Dialogs(xlDialogFormulaReplace).Show to show the
dialog box and you can list the arguments afterwards to control the various
fields and other options. In your case, you would want something like
this...

Application.Dialogs(xlDialogFormulaReplace).Show ActiveCell.Value

which will fill the "Find what" field with the contents of the active cell.
You can also specify what to replace it with by specifying a second text
like so...

Application.Dialogs(xlDialogFormulaReplace).Show _
ActiveCell.Value, "Replacement Text"

Note that I deliberately used a line continuation character to force the
text wrap so that your newsreader wouldn't text wrap it at an odd location.

The arguments you can specify (in this order) a find_text, replace_text,
look_at, look_by, active_cell, match_case, match_byte

You can get the proper values to use for all of them (except for the
active_cell argument which I can find no documentation for) by looking up
the Find Method in the VBA help files. Here is a more complete example
showing one of the many possible combination of arguments (note that I
omitted the active_cell argument)

--
Rick (MVP - Excel)


"plh" wrote in message
...
Hi All,
I am looking for a way to program the Find and Replace window. What I want
to do
is set up a hot key to call the window with the contents of the selected
cell in
the "Find What?" space. (Ordinarily I would just record a macro & alter
what I
got but that recording feature does not seem to record anything when the
Find
and Replace sub-window is used.)
Thank You,
-plh


--
Where are we going and why am I in this HAND BASKET??

Would this possibly help.

Sub CallFRWindow()
Dim FindPhrase As String
Dim ReplacePhrase As String
FindPhrase = ActiveCell.Value
ReplacePhrase = ""
Application.Dialogs(xlDialogFormulaReplace).Show FindPhrase,
ReplacePhrase
End Sub


Regards,

Mika Oukka



"plh" wrote in message
...
Hi All,
I am looking for a way to program the Find and Replace window. What I want
to do
is set up a hot key to call the window with the contents of the selected
cell in
the "Find What?" space. (Ordinarily I would just record a macro & alter
what I
got but that recording feature does not seem to record anything when the
Find
and Replace sub-window is used.)
Thank You,
-plh


--
Where are we going and why am I in this HAND BASKET??

Thanks, that's nice, but I need a little more, and you seem like you would know.
I notice when I do this I don't get the "Find All" button. That is what I would
want. Ideally it would have the sub-window at the bottom already opened with the
results list.
I looked in the sources you mentioned and did not find out how to include that
button. I am doing this for people who are not so computer savvy & so seem to
have a problem doing it the way I do which is to just go Ctrl-C then Ctrl-F then
Ctrl-V
Thanx,
-plh

In article , Rick Rothstein says...

You can use Application.Dialogs(xlDialogFormulaReplace).Show to show the
dialog box and you can list the arguments afterwards to control the various
fields and other options. In your case, you would want something like
this...

Application.Dialogs(xlDialogFormulaReplace).Sho w ActiveCell.Value

which will fill the "Find what" field with the contents of the active cell.
You can also specify what to replace it with by specifying a second text
like so...

Application.Dialogs(xlDialogFormulaReplace).Sho w _
ActiveCell.Value, "Replacement Text"

Note that I deliberately used a line continuation character to force the
text wrap so that your newsreader wouldn't text wrap it at an odd location.

The arguments you can specify (in this order) a find_text, replace_text,
look_at, look_by, active_cell, match_case, match_byte

You can get the proper values to use for all of them (except for the
active_cell argument which I can find no documentation for) by looking up
the Find Method in the VBA help files. Here is a more complete example
showing one of the many possible combination of arguments (note that I
omitted the active_cell argument)

--
Rick (MVP - Excel)


"plh" wrote in message
...
Hi All,
I am looking for a way to program the Find and Replace window. What I want
to do
is set up a hot key to call the window with the contents of the selected
cell in
the "Find What?" space. (Ordinarily I would just record a macro & alter
what I
got but that recording feature does not seem to record anything when the
Find
and Replace sub-window is used.)
Thank You,
-plh


--
Where are we going and why am I in this HAND BASKET??



--
Where are we going and why am I in this HAND BASKET??

Sound like I would be better to through the tedious task of teaching
everyone how to use the "Find Next"- and "Replace"-button.

BR,

Mika Oukka


"plh" wrote in message
...
Thanks, that's nice, but I need a little more, and you seem like you would
know.
I notice when I do this I don't get the "Find All" button. That is what I
would
want. Ideally it would have the sub-window at the bottom already opened
with the
results list.
I looked in the sources you mentioned and did not find out how to include
that
button. I am doing this for people who are not so computer savvy & so seem
to
have a problem doing it the way I do which is to just go Ctrl-C then
Ctrl-F then
Ctrl-V
Thanx,
-plh

In article , Rick Rothstein says...

You can use Application.Dialogs(xlDialogFormulaReplace).Show to show the
dialog box and you can list the arguments afterwards to control the
various
fields and other options. In your case, you would want something like
this...

Application.Dialogs(xlDialogFormulaReplace).Sh ow ActiveCell.Value

which will fill the "Find what" field with the contents of the active
cell.
You can also specify what to replace it with by specifying a second text
like so...

Application.Dialogs(xlDialogFormulaReplace).Sh ow _
ActiveCell.Value, "Replacement Text"

Note that I deliberately used a line continuation character to force the
text wrap so that your newsreader wouldn't text wrap it at an odd
location.

The arguments you can specify (in this order) a find_text,
replace_text,
look_at, look_by, active_cell, match_case, match_byte

You can get the proper values to use for all of them (except for the
active_cell argument which I can find no documentation for) by looking up
the Find Method in the VBA help files. Here is a more complete example
showing one of the many possible combination of arguments (note that I
omitted the active_cell argument)

--
Rick (MVP - Excel)


"plh" wrote in message
...
Hi All,
I am looking for a way to program the Find and Replace window. What I
want
to do
is set up a hot key to call the window with the contents of the selected
cell in
the "Find What?" space. (Ordinarily I would just record a macro & alter
what I
got but that recording feature does not seem to record anything when the
Find
and Replace sub-window is used.)
Thank You,
-plh


--
Where are we going and why am I in this HAND BASKET??



--
Where are we going and why am I in this HAND BASKET??