Using Range.Replace on Formula Cells

Question

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

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.

Do you need the formulas in A1:A10 after the replacement? Are there other formulas that need to stay? If not, you could first replace all formulas with values, `range("A1:A10").value = range("A1:A10").value`, then `r.Replace` would work as you want. As far as I know, there is no possibility to have replace look at values only. — Sun, Jun 27 '16 at 11:50
@Sun There are other formulas in the column that I do not wish to change..................I can always resort to using a loop, but this would be slow for large columns.............thanks for responding.......... — Gary's Student, Jun 27 '16 at 11:56
using `.Find` might speed up your loop (you'd have a do-while-loop) — Sun, Jun 27 '16 at 12:36
@Sun ..........Thank you............it's a good compromise.......... — Gary's Student, Jun 27 '16 at 12:50
It is an ancient thread, but it could be done as initially wonted, but in two steps: Firstly, `What:="=""a"" & ""b"""` and secondly `What:="ab"`... — FaneDuru, Jun 05 '20 at 12:42
I must confess that I became 'cleverer' after I made a comment saying that "I am not sure that **VBA** range Replace function works for formulas". Without testing. I do not remember how such an idea has been sticked on my brain. Documenting myself a little better, I discovered that it works **only** for formulas... — FaneDuru, Jun 05 '20 at 12:48

Vityata · Accepted Answer · 2019-05-11T16:26:10.303

8

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

edited May 11 '19 at 16:26

answered Jun 27 '16 at 12:02

Vityata

42,633
8
55
100

arcadeprecinct · Answer 2 · 2016-06-27T14:46:03.470

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.

Thank you! ..................Using an internal VBA array seems to be he way to go! — Gary's Student, Jun 27 '16 at 14:35

score 2 · Answer 3 · answered Jun 27 '16 at 13:38

2

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

answered Jun 27 '16 at 13:38

Vasily

5,707
3
19
34

score 0 · Answer 4 · answered Jun 06 '20 at 18:35

Even if this is a historical thread, I think it is good to emphasize that fastest method (Replace), works very well for solving the question in discussion, without any iteration. In fact, range 'Replace' method works Only for string formulas... But due to the fact that Formula property of a range without formula returns its text, it works also in that case, too. It only searches and replaces twice, but it needs the exact formula string like What parameter:

Sub Test1_Bis()
    Dim r As Range

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

Using Range.Replace on Formula Cells

4 Answers4