CODEWITHSUNDEEP
excelvbareplacefind
Lewis Kirby
Lewis Kirby

Reputation: 77

Excel VBA Range.Replace operates over entire workbook

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:

  1. Create new workbook with three empty sheets.
  2. In sheet 1 enter "this" in $A$1; in sheet 2 enter "that" in $A$1.
  3. 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.
  4. In sheet 3, select a range eg $A$1:$K$10 and leave this sheet active.
  5. 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!

Upvotes: 2

Views: 5679

Answers (2)

ChristofH
ChristofH

Reputation: 11

I've been looking for a solution for this as well and have been able to use this article https://excel.tips.net/T008801_Changing_Default_Search_Settings.html#comment-form-hd , which basically states to reset the 'find and replace' dialog box. I also updated it to only use values.

Upvotes: 0

BigBen
BigBen

Reputation: 50008

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.

Upvotes: 2

Related Questions