Code to modify find/replace
 

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

Code to modify find/replace
 

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

Code to modify find/replace
 

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

Code to modify find/replace
 

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