Was afraid that may be the case.

Thanks anyway

"Dave Peterson" wrote:

There's no option in VBA that allows you to change this setting.

It's either Sendkeys or a giant leap of faith.


mcphc wrote:

OK the within part of the statment:

Cells.Replace What:="a", Replacement:="b", Within:=xlSheet

does not exist, I just used it as an example of what would be "nice" to have.

If you go into normal excel and click edit|replace and have the options
cexpanded in the find/replace dialog you will see a field called "Within"
giving the option to replace text in the active sheet or all sheets. As far
as I know this can only be changed in excel.

When you use this statement in VB

Cells.Replace What:="a", Replacement:="b"

if "within" is set to "Workbook" all sheets will have "a" replaced by "b".

So if it is set to "Workbook", doing this

For each wks in Activeworkbook.worksheets
wks.Activate
Cells.Replace What:="a", Replacement:="b"
next wks

is not necessary.

My problem is if you what to use this:

Cells.Replace What:="a", Replacement:="b"

on the active sheet only and the "Within" setting is set to "Workbook", all
sheets will be changed.

So how can you make sure the "Within" setting is set to "Sheet" in VB?

Like I said before the closest I've come to doing this is with sendkeys

" wrote:

Hi
Why not do
For each wks in Activeworkbook.worksheets
wks.Activate
Cells.Replace What:="a", Replacement:="b", Within:=xlSheet
next wks

The wks.Activate may not be required.
regards
Paul

On Apr 23, 11:30 am, mcphc wrote:
Is there a way to change the search within feild in the replace command from
"sheet" to "workbook"?

ie something like:

Cells.Replace What:="a", Replacement:="b", Within:=xlSheet

The within option in replace doesn't seem to be covered in VB Cells.Replace
yet Cells.Replace is effected by what ever the Within option is set to using
the normal find/replace command in excel.

The only other idea I have come up with is using sendkeys but sendkeys may
not be very robust.




--

Dave Peterson