Excel VBA FindReplace within Sheet/Workbook persisting

[DrSimon]

I am trying to use VBA to do a FindReplace within a subset of sheets within a workbook and the code like this below works fine as long as the 'Within' parameter in the Find and Replace form is 'Sheet'

    Dim Rng As Range
    Set Rng = ActiveSheet.Range("A1", "ZZ1000")
    Rng.Replace What:="<Before>", Replacement:="After", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False

However if the 'Within' parameter is set to 'Workbook', all examples of "<Before>" in the Workbook are replaced, not just those in the Active Sheet. Within isn't a valid variable in the Replace Method although it is there in the Find and Replace form.

Any ideas on how to resolve this would be gratefully received.
Simon

 

[PHV]

What about use Cells.Replace ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DrSimon]

Very interesting.
I found a solution here:

http://www.vbaexpress.com/forum/showthread.php?t=11444

For reasons that I would love to know, the following code changes the value of 'Workbook' to 'Sheet' in the 'Within' field:

 Set dummy = Worksheets(1).Range("A1:A1").Find("Dummy", LookIn:=xlValues)

So while I can't change the parameter to 'Workbook' by VBA, I now at least have the ability to make the outcome predictable.

A star for anyone who can explain this, especially if there is a way to explicitly do a Workbook Replace using VBA. Find doesn't have 'Within' as an argument, so is this just an undocumented feature?

Simon

 

[PHV]

I think that the GUI version of replace do a For Each loop in the workbook's sheets collection when you set within to workbook.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DrSimon]

Sorry - missing you there. Are you suggesting looping through the workbook's sheet collection and using Cells.Replace within each loop? Why wouldn't that do a full workbook replace in the first loop if Within has already been set to Workbook?

 

[PHV]

As you discovered yourself there is no Within parameter in the VBA version of Replace.
So, in the GUI version you set Within to Workbook, in VBA you loop the Worksheets collection.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886