替换公式中的文本时发现错误 [英] Bug found while replacing text in formulas

问题描述

今天发现了一个非常有趣的错误，即如果它是一个错误。

I discovered a very interesting bug today i.e if it is a bug.

您可以确认是否可以复制？如果它是一个错误，并没有报告，那么我可以这样归档。如果任何Excel-MVP想要将其作为一个错误，我也是可以的。

Can you please confirm if you can replicate it? If it is a bug and has been not reported then I can file it as such. I am also ok if any of the Excel-MVPs want to file it as a bug.

让我们说在单元格 A1 中的sheet1 ，您有一个公式 = $ B $ 2 + $ B $ 3 。现在确保选择您的单元格。现在将此代码粘贴到一个模块中。

Let's say in sheet1 in cell A1, you have a formula = $B$2+ $B$3. Now ensure that your cell is selected. Now paste this code in a module.

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 $ 3 应该已更改为 = $ C $ 3 + $ B $ 3 ，但不即可。 只有当你通过它或如果你按照下一行所提到的那样

Ideally the code should have worked and the = $B$2+ $B$3 should have changed to = $C$3+ $B$3 in the formula bar but it doesn't. It will work only if you step through it or if you do as mentioned in the next line

现在做一件事情就行了。选择除 A1 或 A2 之外的任何单元格。现在，如果您运行代码，代码将按预期方式工作。

Now do one thing. Select any cell other than A1 or A2. Now if you run the code, the code works as expected.

起初我以为我的excel已经疯了，所以我关闭并重新启动它，但我能够在 Excel 2010 中复制上述许多次。

At first I thought that my excel has gone crazy so I closed and re-started it but I was able to reproduce the above in Excel 2010 many number of times.

然后我以为这是一个 .SpecialCells 问题，但上述行为也可以遵守此代码。

Then I thought it is a .SpecialCells issue but the above behavior can be observed with this code as well.

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 

你能复制吗？

推荐答案

p>我复制了你的问题，并通过两种方式解决了问题：

I replicated your issue and got away with it by two ways:

1. 在替换后尝试 ThisWorkbook.Save 。

在替换公式后，选择比A1或A2（选中其公式被替换的单元格）的其他单元。

select other cell than A1 or A2 (cell selected whose formula getting replaced) after replacing formula.

这篇关于替换公式中的文本时发现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！