查找并替换字符串 VBA 中的多个字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21826106/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find and replace multiple character in a string VBA
提问by Viki
I have a large amount of data which i want to replace with the help of macro. I am using code below right now but problem is that it will only replace one value which is D in below code.
我有大量数据，我想在宏的帮助下进行替换。我现在正在使用下面的代码，但问题是它只会替换下面代码中的一个值 D。
Sub Replace()
what = "D"
replc = ""
Sheets("Sheet1").Select
Cells.replace What:=word, Replacement:=replc, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
My sample data is below. As you can see that some value have alphabetical characters, symbol or combination of character at the end or as a cell value, which i want to replace or remove and also some value will be replaced with other values. I create macro with all possibilities to find the required value and replace it.
我的示例数据如下。如您所见，某些值在末尾或作为单元格值具有字母字符、符号或字符组合，我想替换或删除这些值，并且某些值将被其他值替换。我创建了具有所有可能性的宏来找到所需的值并替换它。
0065D2089 2797 3270 3315 0066D2095 2799 3300 3334 0067D2105 2802 3582aXa2089 – 2812 3307 3383 2095 2111 1164D3315 3385 2105 2112 1165D3334 3400 2110 2114 2841 3336F3507 0771N2121 2881 3365 3515N*0908P* 2130 2883 3372X3548 0913P2131 2913 3373 3559 2111 2143 2915 3373 3574
0065D2089 2797 3270 3315 0066D2095 2799 3300 3334 0067D2105 2802 3582aX一个2089年至2812年3307 3383 2095 2111 1164D3315 3385 2105 2112 1165D3334 3400 2110 2114 2841 3336F3507 0771N2121 2881 3365 3515N* 0908P* 2130 2883 3372X3548 0913P2131 2913 3373 3559 2111 2143 2915 3373 3574
You also notice that there are some lower case characters as well. Right now i was trying to use IFcondition with Findbut could not succeed. I am making above macro with every possible condition to replace and run it from another macro which is something like below algo.
您还注意到还有一些小写字符。现在我试图在Find 中使用IF条件，但没有成功。我正在用所有可能的条件制作上面的宏，以从另一个类似于下面算法的宏中替换和运行它。
IF find "D" Then
Run macro "Replace"
Elseif find "X"
Run macro "Replace"
Elseif find "F"
Run macro "Replace"
and so on....
but if the macro didn't find required character in the sheet then it skips to next step and so on. I want this so bad. I have to perform this replace routine on number of times.
但是如果宏没有在工作表中找到所需的字符，那么它会跳到下一步等等。我想要这个。我必须多次执行此替换例程。
So any help will be appreciated.
所以任何帮助将不胜感激。
Thanks
谢谢
回答by Rawrcasm
You can add/remove necessary characters from the toRemove()array.
您可以从toRemove()数组中添加/删除必要的字符。
Sub replaceChars()
Dim toRemove()
toRemove() = Array("D", "X", "F")
For Each itm In toRemove()
For Each iCell In ActiveSheet.UsedRange
iCell.Replace What:=itm, Replacement:="", MatchCase:=True
Next iCell
Next itm
End Sub
