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

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

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

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

With ActiveWorkbook.Sheets(1)
.Range("A1") = _
Application.WorksheetFunction.Substitute(.Range("A 1"), "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