Limiting Find and Replace to only specific range

[Moosles]

I am doing a Find/replace which replaces all text in quotation marks within the range ReplacementRange with a string of text called ReplacementText which is created by some separate code . My current code is:

Range("ReplacementRange").Replace What:="""*""", Replacement:="""" & ReplacementText & """"

This works fine, however I was working on something totally separate and manually did a Find/Replace throughout the Workbook (selecting "Workbook" from the Within menu in the Find/Replace options). When I then ran the above code, the Find/Replace in the macro was no longer limited to the ReplacementRange, but instead performed the Find/Replace across the entire workbook, which just wrecked it. How do I limit the Find/Replace range to only the specified range?

 

[coulombevin]

Hi Moosles,

To limit a manual find and replace you must:

1. select the range by clicking the first cell
2. holding SHIFT click on the last cell
3. open replace (CTRL + H)
4. put "*" in search space
5. put the replacement text in replace space
6. select SHEET for the section under called IN :
7. Click on replace all

Bests regards,

Vincent

 

[Moosles]

Sorry, I should have been clearer. I need to limit the Find/Replace in the macro code to only Find/Replace within a set range. If I have manually done a Find/Replace within the entire Workbook prior to running the Find/replace macro, when I do run the Find/Replace macro it does the Find/Replace within the entire Workbook which basically makes my workbook unusable.

 

[Joe4]

If you run the code as you show it in your original post, it SHOULD limit it to just the range you have specified ("Replacement Range").
It wouldn't hurt to add the Sheet reference before the named range, though I believe named ranges are already attached to sheets, and I don't think you can have two named ranges in the same workbook with the same name, unless they are in different scopes (see: Excel range names: What you need to know - FM.).

Where exactly is this VBA code you are using located?
What is the name of the module that you have placed it in, and what is the other VBA code that you have around it?

 

[RoryA]

Running any kind of Find first should reset that option - for example:

With Range("ReplacementRange")
   .Find ""
   .Replace What:="""*""", Replacement:="""" & ReplacementText & """"
end with

 

[Moosles]

Hi Joe,

I have just tested this again, this time in a much simplified file with 3 tabs, A, B & C, with a formula displaying text in the Replacement range (found on tab A)

="I want to change this text"

, and the formula

="But not this text"

scattered around in all 3 tabs. With the same conditions as in my initial query, I get the exact same output of everything throughout the workbook being replaced.

If I have not done a Workbook-wide Find/Replace prior to running the code below, it works just fine:

Option Explicit

Sub Test()

Dim ReplacementText As String

ReplacementText = "ABCDEFG"
Range("ReplacementRange").Replace What:="""*""", Replacement:="""" & ReplacementText & """"

End Sub

If, however, I do a manual Find/Replace for just some nonsense text, where "Workbook" is selected in the Within option before running this code, then all text everywhere gets replaced.

The code I'm using is in a module called ModGeneral, this is the only Find/Replace in the entire module, this code is part of a loop where various checks use VLOOKUPs or XLOOKUPs which use the text that is being replaced to determine if a particular section of my workbook needs to be updated or not.

I see that Rory has provided a solution which works perfectly for my needs so I will close this thread and mark his answer as the solution. Thanks anyway!