Find and Replace Within a Range Versus Workbook - Suggestion

[Paige]

If you are doing find/replace in VBA and having issues with the code
replacing within the workbook versus the specified range in your code (which
is a known problem), then try this. First do a 'dummy' find, then do your
desired find/replace. For example:

'Your 'dummy' find:
Dim r As Range
Set r = Worksheets("Sheet1").Range("G3:G65").Find(What:="ZORRO",
LookIn:=xlFormulas)

‘Then do your ‘real’ find/replace, such as:
Worksheets("Sheet1").Range("G3:G65").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

This resets the 'Within' field in Excel's Find and Replace box to 'Sheet',
so your code will do the find/replace only within the specified range. This
has been working for me so wanted to post in case it might help others.
Thanks.

 

[Patrick Molloy]

i don't see then need for this.

Dim target As Range
Set target = Worksheets("Sheet1").Range("G3:G65")
target.Replace "Zorro", "Zapotec"


like much VBA, there's never really much need to actually select ranges or
activate sheets.




works perfectly well replacing "Zorro" only as excpected. The word, whether
elsewhere on teh same or other sheets is not affected

 

[Paige]

I was using the find/replace to remove extra spaces, as it was quicker. So
what was happening was that if the user had 'Within Workbook' selected in the
Find/Replace box, it would ignore my selected range and use the 'Within
Workbook'. You're right in that if what I want to find/replace is unique, no
need. But for spaces, I couldn't find another way that was better in terms
of processing time. If you have some suggestions for this, would appreciate
them. And yes, there was no need to actually do the 'select' first; is older
code done when I was just learning, and am working on spiffying up. Thanks!