Использование Range.Replace на ячейках формулы

У меня есть короткий список значений от A1 до A10:

A4 содержит строку ab и A5 содержит формулу:

="a" & "b"

Если я запустил:

Sub Test1()
    Dim r As Range

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

изменяется только A4.

Как я могу заставить Replace Method работать для обоих случаев?

EDIT # 1:

Я всегда могу использовать цикл для проверки/замены элемента за пунктом, но .Replace выполняется намного быстрее. Я полагаю, что я мог бы создать и использовать временный автофильтр, но это кажется экстремальным.

Ответ 1

Это то, что я создал:

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

Ответ 2

Интересная проблема, похоже, что опция LookIn будет приятной.

Я попробовал несколько вещей (все же, используя петли). Я отключил проверку экрана для всех тестов.

Использование Replace:

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

используя 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

используя простой цикл:

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

Использование лучшего цикла:

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

Использование массива для поиска:

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

Replace и метод массива были самыми быстрыми для поиска диапазона, и я не заметил разницы в скорости. Тем не менее, запись в ячейки значительно замедлила цикл, когда было много заметок (для меня было заметно около 5000 замен из 1 000 000 значений). Find страдал от большей замены, а остальные два цикла были намного медленнее при поиске.

Заключение: использование внутреннего массива - лучший способ (о котором я могу думать). Он даже превосходит все формулы сначала (r.Value = r.Value).

Возможно сохранение всех вхождений и замена их после того, как цикл может ускорить работу.

Ответ 3

также вы можете использовать это:

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

но этот вариант удалит все формулы в диапазоне