Using Range.Replace on Formula Cells

calendar_today Asked Jun 27, 2016
thumb_up 7 upvotes
history Updated April 16, 2026

Question posted 2016 · +10 upvotes

I have a short list of values from A1 through A10:

enter image description here

A4 contains the string ab and A5 contains the formula:

="a" & "b"

If I run:

Sub Test1()
    Dim r As Range

    Set r = Range("A1:A10")
    r.Replace What:="ab", Replacement:="x"
End Sub

only A4 gets modified.

How can I get the Replace Method to work for both cases ??

EDIT#1:

I can always use a loop to examine/replace item-by-item, but .Replace is much faster. I suppose that I could build and use a temporary AutoFilter, but this seems extreme.

Accepted answer +7 upvotes

This is what I have created:

Option Explicit

Sub testme()

    Dim my_cell     As Range
    Dim str_text    As String

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

End Sub

Excel VBA objects referenced (3)

  • Range — Refer to Cells by Using a Range Object
  • Range — Delete Duplicate Entries in a Range
  • Range.Replace — Create or Replace a Worksheet

Top excel-vba Q&A (6)

+7 upvotes ranks this answer #101 out of 136 excel-vba solutions on this site .
excel-vba