Excel VBA Range.Replace 对整个工作簿进行操作 [英] Excel VBA Range.Replace operates over entire workbook

问题描述

我在使用 Range.Replace 时遇到问题.众所周知，当在 Excel 工作表界面中我们可以 Find &Replace with Match Case 设置为 True 或 False，当我们再次查找或替换时，Excel 会记住我们的 Match Case 设置.如果我们使用 Find &从 VBA 替换，以及 LookIn、LookAt 等其他设置.

I'm having a problem with Range.Replace. As we all know, when in the Excel worksheet interface we can Find & Replace with Match Case set to True or False, and when we Find or Replace again Excel remembers our Match Case setting. The same is true if we use Find & Replace from VBA, and for other settings such as LookIn, LookAt.

注意 Find &使用 Excel 工作表界面替换还记得内部"选项(设置为工作表"或工作簿").我的问题是，如果我使用了 Find &从工作表界面替换，我已将Within"选项设置为Workbook"(而不是默认的Sheet")，然后 Excel VBA 也会记住该选项，并执行 Workbook-level Replace 即使我是调用 Range.Replace(即我只希望 Replace 在特定范围内发生).

Note that Find & Replace using the Excel worksheet interface also remembers the "Within" option (set to either "Sheet" or "Workbook"). My problem is that if I have used Find & Replace from the worksheet interface and I have set the "Within" option to "Workbook" (instead of the default "Sheet"), then Excel VBA also remembers that option, and does a Workbook-level Replace even if I am invoking Range.Replace (ie I only want the Replace to happen over a specific range).

我可以重现如下:

1. 使用三张空白工作表创建新工作簿.
2. 在表 1 中，在 $A$1 中输入this"；在表 2 中输入 $A$1 中的那个".
3. 在内部"设置为工作簿"的情况下查找垃圾"，并保留所有其他选项未选中并保持默认.这将一无所获；关闭对话.
4. 在表 3 中，选择一个范围，例如 $A$1:$K$10 并保持此表处于活动状态.
5. 打开VB编辑器并输入:Selection.Replace "th","help".

你会发现(嗯，我做到了)即使我们使用 Range.Replace 非活动工作表 1 和 2 上的th"实例已更改 - 看起来范围内"选项已被记住，范围.替换"的范围"部分被忽略.

You will find (well, I did) that even though we are using a Range.Replace the instances of "th" on the inactive sheets 1 and 2 have been changed -- so it seems that the "Within" choice has been remembered and the "Range" part of "Range.Replace" is being ignored.

有没有办法覆盖它?还是强制 Excel 忘记内部"设置?

Is there any way to override this? Or force Excel to forget the "Within" setting?

我在 Windows 10 上使用 Office Professional Plus 2016.

I am using Office Professional Plus 2016 on Windows 10.

请帮忙！

推荐答案

正如你提到的，Find and Replace 还记得 "Within" 选项，在 Range.Find 中没有(可选)参数 或 Range.Replace.

As you mention, Find and Replace also remembers the "Within" option, which there is no (optional) parameter for in Range.Find or Range.Replace.

如果您想绝对保证替换仅在有问题的 Sheet 上，那么将Within"选项从Workbook"重置为Sheet"的技巧是执行 <在尝试 Range.Replace 之前，首先要 code>Range.Find.

If you want to absolutely guarantee that the replace is only on the Sheet in question, the trick then to reset the "Within" option to "Sheet" from "Workbook" is to do a Range.Find first, before attempting to Range.Replace.

继续您的示例，下面的代码将仅替换 Sheet1 上的 th.但是，如果您注释掉第 2 行，通过复制上面的第 3 步将Within"选项设置为Workbook"，然后重新运行此代码，它将替换两个 Sheet1 上的 th 和 Sheet2.

Continuing with your example, the code below will only replace th on Sheet1. However, if you comment out the 2nd line, set the "Within" option to "Workbook" by replicating your 3rd step above, and then rerun this code, it will replace th on both Sheet1 and Sheet2.

Sub SheetOnlyReplace()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
    Dim rng As Range: Set rng = ThisWorkbook.Sheets("Sheet1").Cells.Find("Th")
    ws.Cells.Replace "th", "help", xlPart, xlByRows, False, False, False, False
End Sub

而且由于查找/替换可能是不可预测的，正如这所演示的，您可以将范围中的值读入一个数组，对其进行迭代进行更改，然后将修改后的值写回您的工作表，如 @Mathieu Guindon.

And because Find/Replace can be unpredictable, as this demonstrates, you could just read the values from your range into an array, iterate over it making changes, and then write the modified values back to your worksheet, as proposed by @Mathieu Guindon.

这篇关于Excel VBA Range.Replace 对整个工作簿进行操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！