Excel VBA Range.Replace operates over entire workbook

Question

I'm having a problem with Range.Replace. As we all know, when in the Excel worksheet interface we can Find & Replace with Match Case set to True or False, and when we Find or Replace again Excel remembers our Match Case setting. The same is true if we use Find & Replace from VBA, and for other settings such as LookIn, LookAt.

Note that Find & Replace using the Excel worksheet interface also remembers the "Within" option (set to either "Sheet" or "Workbook"). My problem is that if I have used Find & Replace from the worksheet interface and I have set the "Within" option to "Workbook" (instead of the default "Sheet"), then Excel VBA also remembers that option, and does a Workbook-level Replace even if I am invoking Range.Replace (ie I only want the Replace to happen over a specific range).

I can reproduce as follows:

Create new workbook with three empty sheets.
In sheet 1 enter "this" in $A$1; in sheet 2 enter "that" in $A$1.
Do a Find for "junk" with "Within" set to "Workbook" and leave all other options unchecked and default. This will find nothing; close the dialogue.
In sheet 3, select a range eg $A$1:$K$10 and leave this sheet active.
Open the VB Editor and enter: Selection.Replace "th","help".

You will find (well, I did) that even though we are using a Range.Replace the instances of "th" on the inactive sheets 1 and 2 have been changed -- so it seems that the "Within" choice has been remembered and the "Range" part of "Range.Replace" is being ignored.

Is there any way to override this? Or force Excel to forget the "Within" setting?

I am using Office Professional Plus 2016 on Windows 10.

Please help!

Seems like the answer is to Find the value first on the sheet in question, and then Replace.... quick Google search: vbaexpress.com/forum/…, and ozgrid.com/forum/forum/help-forums/excel-general/… — BigBen
Specify all the optional parameters, you'll override the values "remembered" from the previous Range.Find call. — Mathieu Guindon
I suspect part of the problem is invoking Range.Replace over Selection. — Mathieu Guindon
@MathieuGuindon - no I can replicate the issue without using Selection. I'm not sure I'd call it a bug, but since Within in the Find/Replace dialog is not an optional parameter, I think the VBA solution to coerce the replace to a Sheet is to do Sheet.Range.Find first, and then Replace. — BigBen
@BigBen in any case, invoking Range.Find or Range.Replace without specifying the optional parameters, is begging for things to go wrong: silly as it is, these methods are literally specified to "remember" the values for these optional parameters, and to reuse them when not specified (regardless of the "Within" issue). Using Sheet1.Cells.Find effectly seems to "resets" the "Within" from "Workbook" to "Sheet" when I try it, hence my suspicion about Selection, which I hardly ever use at all. — Mathieu Guindon

BigBen BigBen · Accepted Answer · 2018-08-06T17:25:40

As you mention, Find and Replace also remembers the "Within" option, which there is no (optional) parameter for in Range.Find or Range.Replace.

If you want to absolutely guarantee that the replace is only on the Sheet in question, the trick then to reset the "Within" option to "Sheet" from "Workbook" is to do a Range.Find first, before attempting to Range.Replace.

Continuing with your example, the code below will only replace th on Sheet1. However, if you comment out the 2nd line, set the "Within" option to "Workbook" by replicating your 3rd step above, and then rerun this code, it will replace th on both Sheet1 and Sheet2.

Sub SheetOnlyReplace()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
    Dim rng As Range: Set rng = ThisWorkbook.Sheets("Sheet1").Cells.Find("Th")
    ws.Cells.Replace "th", "help", xlPart, xlByRows, False, False, False, False
End Sub

And because Find/Replace can be unpredictable, as this demonstrates, you could just read the values from your range into an array, iterate over it making changes, and then write the modified values back to your worksheet, as proposed by @Mathieu Guindon.

Excel VBA Range.Replace operates over entire workbook

1 Answers