find replace within sheet or workbook

[Guest]

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.

 

[NickHK]

Not sure where your "Within" comes from. According to help on this subject,
(may vary slightly depending on your version of XL):

"expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase,
MatchByte, SearchFormat, ReplaceFormat)

expression Required. An expression that returns a Range object.
What Required Variant. The string you want Microsoft Excel to search for.
Replacement Required Variant. The replacement string.
LookAt Optional Variant. Can be one of the following XlLookAt constants:
xlWhole or xlPart....."

So if you want to perform the replace for all WSs in the WB, loop through
the WS collection

Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
WS.Cells.Replace "a", "b"
Next

NickHK

 

[paul.robinson]

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

 

[Guest]

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

 

[Dave Peterson]

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

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

 

[Guest]

Was afraid that may be the case.

Thanks anyway

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

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