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
– Sun, Commented Jun 27, 2016 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
– Gary's Student, Commented Jun 27, 2016 at 11:56
using .Find might speed up your loop (you'd have a do-while-loop) — Sun
– Sun, Commented Jun 27, 2016 at 12:36
@Sun ..........Thank you............it's a good compromise.......... — Gary's Student
– Gary's Student, Commented Jun 27, 2016 at 12:50

Vityata · Accepted Answer · 2019-05-11 16:26:10Z

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, 2019 at 16:26

answered Jun 27, 2016 at 12:02

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

arcadeprecinct · Accepted Answer · 2016-06-27 14:46:03Z

6

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.

edited Jun 27, 2016 at 14:46

answered Jun 27, 2016 at 13:57

arcadeprecinct

3,7771 gold badge14 silver badges18 bronze badges

1 Comment

Gary's Student Over a year ago

Thank you! ..................Using an internal VBA array seems to be he way to go!

Vasily · Accepted Answer · 2016-06-27 13:38:45Z

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, 2016 at 13:38

Vasily

5,7923 gold badges22 silver badges36 bronze badges

Comments

FaneDuru · Accepted Answer · 2020-06-06 18:35:40Z

0

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

answered Jun 6, 2020 at 18:35

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Collectives™ on Stack Overflow

Using Range.Replace on Formula Cells

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related