Ошибка при замене текста в формулах

Я обнаружил очень интересную ошибку сегодня, если это ошибка .

Можете ли вы подтвердить, можете ли вы его воспроизвести? Если это ошибка и не сообщается, я могу записать ее как таковой. Я тоже в порядке, если какой-либо из Excel-MVP хочет записать его как ошибку.

Скажем в sheet1 в ячейке A1, у вас есть формула = $B$2+ $B$3. Теперь обеспечить, что ваша ячейка выбрана. Теперь вставьте этот код в модуль.

Sub Sample()
    Dim r As Range, sPre As String, sAft As String
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    sPre = "$B$2": sAft = "$C$3"

    On Error Resume Next
    Set r = ws.Range("A1:A2").SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0

    If Not r Is Nothing Then r.Replace what:=sPre, _
                                       replacement:=sAft, _
                                       lookat:=xlPart, _
                                       MatchCase:=False
End Sub

В идеале код должен был работать, а = $B$2+ $B$3 должен был быть изменен на = $C$3+ $B$3 в строке формулы, но не. Он будет работать только в том случае, если вы пройдете через него или если вы сделаете, как указано в следующей строке

Теперь сделайте одно. Выберите любую ячейку, отличную от A1 или A2. Теперь, если вы запустите код, код работает так, как ожидалось.

Сначала я подумал, что мой excel сошел с ума, поэтому я закрыл его и перезапустил, но я смог воспроизвести выше в Excel 2010 много раз.

Тогда я подумал, что это проблема .SpecialCells, но описанное выше поведение можно наблюдать и с этим кодом.

Sub Sample()
    Dim r As Range, sPre As String, sAft As String
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    sPre = "$B$2": sAft = "$C$3"

    Set r = ws.Range("A1:A2")

    r.Replace what:=sPre, _
              replacement:=sAft, _
              lookat:=xlPart, _
              MatchCase:=False
End Sub

Можете ли вы его воспроизвести?

Ответ 1

Я воспроизвел вашу проблему и ушел от нее двумя способами:

Попробуйте ThisWorkbook.Save после замены.
выберите другую ячейку, чем A1 или A2 (ячейка выбрана с заменой формулы) после замены формулы.

Ответ 2

Хотя многие альтернативы были предложены в других ответах, например

Выберите другую ячейку с помощью кода
Сохранить книгу

Если я не хочу выбирать ячейку или сохранять книгу, есть ли альтернативный способ, который лучше, чем предыдущие два? Да, есть. Просто попробовал это, и он работает

Sub Sample()
    Dim r As Range, sPre As String, sAft As String
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    sPre = "$B$2": sAft = "$C$3"

    On Error Resume Next
    Set r = ws.Range("A1:A3").SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0

    If Not r Is Nothing Then r.Replace what:=sPre, _
                                       replacement:=sAft, _
                                       lookat:=xlPart, _
                                       MatchCase:=False

    r.Formula = r.Formula
End Sub

Но остается вопрос, что Formula Bar должен быть обновлен в исходном сценарии, но он не

Ответ 3

Я частично могу реплицировать его, также Excel 2010.

Если я запустил макрос с выбранной ячейкой, используя параметр "Выполнить" в редакторе Visual Basic, значение в ячейке изменится, чтобы отразить новую формулу, но формула в строке формул не отображается как обновленная. Но он должен быть обновлен, потому что результат изменился. Если я выйду из ячейки и вернусь, появится обновленная формула и покажет, что поиск/замена сработали.

Если я пройду через макрос в окне VBA, панель формул будет отображаться как обновленная, пока выполняется макрос.

Если я запустил макрос из окна Excel, используя Макросы → Просмотр → Выполнить, панель формул будет отображаться как обновленная, пока выполняется макрос.

Если я добавлю r.Select в последнюю строку макроса, запустите его из VBA.

Если я запустил макрос, чтобы он не обновлял панель формул, а затем щелкните на панели формул, панель формул отобразит старую формулу, но содержимое ячейки изменится, чтобы отобразить новую формулу вместо ответа.

Изменить: поведение выглядит одинаково в Excel 2013