Using Range.Replace on Formula Cells

This is what I have created:

Option Explicit

Sub TestMe()

    Dim myCell As Range
    Dim myText As String

    For Each myCell In Worksheets(1).Range("A1:A10")
        If InStr(myCell.Text, "ab") > 0 Then
            myText = myCell.Text
            myCell = Replace(myText, "ab", "x")
            myCell.Value = myText
        End If
    Next myCell

End Sub

also you can use this:

Sub ThereIsAnotherOneVariant()
    With [A1:A10]
        .Value2 = .Value2
        .Replace "ab", "x"
    End With
End Sub

but this variant will remove all formulas in the range

Interesting problem, it seems that a LookIn option would be nice.

I tried a few things (all using loops however). I disabled screenupdating for all the tests.

Using Replace:

r.Replace what:="ab", Replacement:="x"

using Find:

Dim c As Range
Set c = r.Find(what:="ab", LookIn:=xlValues)
While Not (c Is Nothing)
    c.Value = "x"
    Set c = r.FindNext
Wend

using a simple loop:

Dim i As Long
For i = 1 To 10
    If Cells(i, 1).Value = "ab" Then Cells(i, 1).Value = "x"
Next i

Using a better loop:

Dim c as Range
    For Each c In r.Cells
    If c.Value = "ab" Then c.Value = "x"
Next c

Using an array to search:

Dim v As Variant
Dim i as Long
v = r.Value
For i = 1 to 10
    If v(i,1) = "ab" Then Cells(i,1).Value = "x"
next i

The Replace and array method were the fastest to search the range and I didn't notice any speed difference. However, the writing to the cells slowed down the loops considerably when there were many replacements to do (it got noticeable somewhere around 5000 replacements out of 1,000,000 values for me). The Find suffered heavy from more replacements and the other two loops were much slower when searching.

Conclusion: Using an internal array is the best way (that I can think of. It even beats removing all the formulas first (r.Value = r.Value).

Maybe saving all the occurrences and replacing them after the loop could speed things up further.