Excel - VBA：自动替换另一个词（使用What / Replacement） [英] Excel - VBA : automatically replace words with another (using What/Replacement)

问题描述

我希望我的代码能够用列表中的其他单词自动替换一些单词。
到目前为止，我发现如何用另一个字替换一个单词。例如，如果我想要将大道一词改为Ave.我使用：

I would like my code to automatically replace some words from a list by other words. So far, I found how to replace one word by another. For example, if I want the word "Avenue" to be replaced by the word "Ave." I use :

Worksheets("sMain").Columns("D").Replace _
What:="Avenue", Replacement:="Ave.", _
SearchOrder:=xlByColumns, MatchCase:=True

它有效。
现在我想通过用另外一个字替换一个以上的单词来提高效率。例如，如果我键入Avenue或avenue或ave，它将被Ave.替换。
我一直在尝试这个但没有成功（编译错误）：

It works. Now I would like to make it more efficient by replacing more than one word by another. For example, if I type "Avenue" or "avenue" or "ave.", it will be replaced by "Ave.". I have been trying this but with no success (compilation error) :

Worksheets("sMain").Columns("D").Replace _
What:="Avenue", Replacement:="Ave.", _
What:="avenue", Replacement:="Ave.", _
What:="ave.", Replacement:="Ave.", _
SearchOrder:=xlByColumns, MatchCase:=True

有什么想法如何使它成为可能？
谢谢

Any idea on how to make it possible? Thanks

推荐答案

您可以在这样的循环中执行

You could do it in a loop like this

ary = Array("Avenue", "avenue", "ave.")
For Each wd In ary
    Columns("D").Replace What:=wd, Replacement:="Ave.", SearchOrder:=xlByColumns, MatchCase:=True
Next

您也可以将MatchCase设置为False，因为您不需要两个大小写avenue。

You may also like to set MatchCase to False as you won't need the two upper and lower case "avenue"'s .

这篇关于Excel - VBA：自动替换另一个词（使用What / Replacement）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！