Code to modify find/replace

[mcphc]

I need a way to change the "Within" field from Workbook to Sheet in the find
and replace (options) command.

Is there a way to force "Within" to be Sheet in Vb?

Thanks
Clayton

[Ingolf]

Hi Clayton

to replace "a" by "b" in sheet 1 of the active workbook use

ActiveWorkbook.Sheets(1).Cells.Replace What:="a", Replacement:="b"

Regards
Ingolf

[mcphc]

That's not quite what I ment.

If you click Edit|Replace in excel and then click options you will see a
pull down menu to change the Within field to "Sheet" or "Workbook". I need to
make sure it is set to "Sheet" before using something like:

ActiveWorkbook.Sheets(1).Cells("A1").Replace What:="a", Replacement:="b".

If the Within field is set to Workbook the above will replace EVERY
occurance of "a" to "b" in the ENTIRE workbook instead of just cell "A1".

Try it

Clayton

"Ingolf" wrote:

> Hi Clayton
>
> to replace "a" by "b" in sheet 1 of the active workbook use
>
> ActiveWorkbook.Sheets(1).Cells.Replace What:="a", Replacement:="b"
>
> Regards
> Ingolf
>
>

[Ingolf]

OK, I see. Another suggestion. Use a WorksheetFunction instead.

With ActiveWorkbook.Sheets(1)
.Range("A1") = _
Application.WorksheetFunction.Substitute(.Range("A1"), "a", "b", 1)
End With

Ingolf


mcphc schrieb:

> That's not quite what I ment.
>
> If you click Edit|Replace in excel and then click options you will see a
> pull down menu to change the Within field to "Sheet" or "Workbook". I need to
> make sure it is set to "Sheet" before using something like:
>
> ActiveWorkbook.Sheets(1).Cells("A1").Replace What:="a", Replacement:="b".
>
> If the Within field is set to Workbook the above will replace EVERY
> occurance of "a" to "b" in the ENTIRE workbook instead of just cell "A1".
>
> Try it
>
> Clayton
>
> "Ingolf" wrote:
>
> > Hi Clayton
> >
> > to replace "a" by "b" in sheet 1 of the active workbook use
> >
> > ActiveWorkbook.Sheets(1).Cells.Replace What:="a", Replacement:="b"
> >
> > Regards
> > Ingolf
> >
> >