Replace in a sheet and not the entire workbook

[ammx]

Hi Charlize,

thank you very much for your help. You saved my day

I analyzed your code a bit further and could extract it to a single miracle line that does the job:

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

With that line just before any REPLACE operations, the EDIT FIND/REPLACE dialog is resetted to look within 'sheet' and not within 'workbook'. So just what I needed.

Here is my code snippet that incorporates the changes. Just watch out for the lines with 'dummy'...

Public Sub NowItWorks()
    ' Now it works, the 'find dummy' ensures that the find is reset to search by 'sheet'
    Dim dummy As Range
    Worksheets(1).Range("a1") = "Replace 1"
    Worksheets(2).Range("a1") = "Replace 2"
    Worksheets(3).Range("a1") = "Replace 3"
    MsgBox "Click OK to start replacing"
    'Reset the EDIT FIND function to 'sheet' instead of 'workbook'
    Set dummy = Worksheets(1).Range("A1:A1").Find("Dummy", LookIn:=xlValues)
    Worksheets(1).Cells.Replace What:="Replace*", Replacement:="This is sheet 1"
    Worksheets(2).Cells.Replace What:="Replace*", Replacement:="This is sheet 2"
    Worksheets(3).Cells.Replace What:="Replace*", Replacement:="This is sheet 3"
End Sub

Special thanks also from 'Kalle', our dog. He is from Belgium and sends warm greetings back home .

Michael

P.S.

I still believe that this nasty behaviour is a real bug in the Excel VBA. However, this work around is acceptable to me.