Macro Replace

The code in the below.
I attached test Excel file , please look at D10 in DP sheet before and after  run the code
it shoul does not change.
Thanks

Sub ChangeStr()

Dim WS As Worksheet, Wst As Worksheet
Dim Cell As Range
Dim LookFor As String
Dim ReplaceBy As String
Dim FirstAddress As String
Dim I As Long

Set WS = Sheets("MT")
LookFor = WS.Range("C6")
ReplaceBy = WS.Range("C5")

For Each Wst In ActiveWorkbook.Worksheets
    If Wst.Name <> WS.Name Then
        With Wst.UsedRange
            Set Cell = .Find(LookFor, LookIn:=xlValues, lookat:=xlPart)
            If Not Cell Is Nothing Then
                FirstAddress = Cell.Address
                Do
                    Cell = Replace(Cell, LookFor, ReplaceBy)
                    I = I + 1
                    On Error Resume Next
                    Set Cell = .FindNext(Cell)
                Loop While Not Cell Is Nothing And Cell.Address <> FirstAddress
                On Error GoTo 0
            End If
        End With
    End If
   
Next Wst

 
MsgBox ("Total of " & I & " Cells containing instances of '" & LookFor & "' where found and replaced by '" & ReplaceBy & "' successfully.")

     
End Sub
 Test.xlsm Test.xlsm

The code above looks for the string found in cell C6 on sheet MT and replaces this string with the contents of cell C5 on sheet MT

In your test file the find value is Yahoo.com and the replace value is Google.com and as expected the string Yahoo.com is replaced with Google.com


Note - The macro runs on the currently selected page when it is started

So I am not sure what the problem is. Could you please provide more info as to what you are expecting

Michael

Before run the macro In D5 "=E10"
After  run the macro D5 " <a href='http://Yahoo.com' 'target='_blank'></a>"
I do not want to change in D5

After  run the macro in D5 " <a href='http://Google.com' 'target='_blank'></a>"

Why the macro change in D5?

If I understand correctly, you need to change this line:

                    Cell = Replace(Cell, LookFor, ReplaceBy)

To this:

If not cell.hasformula then Cell = Replace(Cell, LookFor, ReplaceBy)

rorya:
If not cell.hasformula then Cell = Replace(Cell, LookFor, ReplaceBy)
1-Made still in the loop, does not work.

2-What I need to  change any where formula or value because I use this value in some formulas,
 but I want the macro doesn't change in the refernce,

In that case I think you need to replace this line:

Set Cell = .Find(LookFor, LookIn:=xlValues, lookat:=xlPart)

Select allOpen in new window

with this:

Set Cell = .Find(LookFor, LookIn:=xlFormulas, lookat:=xlPart)

Select allOpen in new window

in your initial code.

You could also simply run a Replace on all cells as that will look in formulas automatically.

OK ,good  the macro does not change in the refrence.
but If I have that value in formula like   ="javascript:showLightbox('"&CURL("http://Yahoo.com")&"',1800,1800)"
the macro changes to javascript:showLightbox('"&CURL("http://Yahoo.com")&"',1800,1800)
converted it from formula to value

I can't see why that would happen. Can you post a sample?

I attached test file, and You can test the macro
please watch D18 in ssheet DP, before run the macro is forumla, after run it, it be value

 Test.xlsm

Sorry, I uploaded the last version
please look at this Test.xlsm

D18 was a value to start with? D12 starts as a formula and remains one (though you have calculation set to manual so it doesn't update.)

Thank you