vba 如何在Excel中查找和替换多个值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19792986/
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
How to find and replace multiple values in Excel?
提问by Adrian
I have for example a column of 18000 domains and another list with 210.000 domain which those 18000 domains can be found. I need to do smth like CTRL + H, for find and replace, and in the find field I need to add the entire 18000 domains: smth like * domain1.com *, * domain2.com *, * domain3.com * and replace them with blank space. Tried this with find and replace from excel but it doesn't work to add more than 1 value in the Find field. How can i do it for multiple values?
例如，我有一个包含 18000 个域的列和另一个包含 210.000 个域的列表，可以找到这些 18000 个域。我需要像 CTRL + H 那样做查找和替换，在查找字段中我需要添加整个 18000 个域：smth like * domain1.com *, * domain2.com *, * domain3.com * 并替换他们有空格。尝试使用从 excel 中查找和替换进行此操作，但无法在“查找”字段中添加 1 个以上的值。我怎样才能为多个值做到这一点？
采纳答案by Sam
VBA solution
VBA解决方案
You will need to change the two sheet references (data and edit sheet) data = source, edit = destination. I've also set the replace string to a variable so you can change this from an empty string if required.
您将需要更改两个工作表引用（数据和编辑工作表）数据 = 源，编辑 = 目标。我还将替换字符串设置为变量，因此您可以根据需要将其从空字符串更改。
If you need any other logic (ie Trim the strings before compare or a change to the strings case comparison) the code should be reasonably easy to tweak.
如果您需要任何其他逻辑（即在比较之前修剪字符串或更改字符串大小写比较），代码应该相当容易调整。
Hope this helps.
希望这可以帮助。
Sub ReplaceValues()
Dim dataSht As Worksheet
Dim editSht As Worksheet
Dim dataRange As Range
Dim dataColumn As Long
Dim editColumn As Long
Dim dataEndRow As Long
Dim editEndRow As Long
'sheet that holds all the values we want to find
Set dataSht = Sheet2
'sheet we want to edit
Set editSht = Sheet1
Dim replaceValue As String
'replace value is empty string
replaceValue = ""
'set the column of the data sheet to A
dataColumn = 1
'set the colmun of the sheet to edit to A
editColumn = 1
dataEndRow = dataSht.Cells(dataSht.Rows.count, dataColumn).End(xlUp).Row
editEndRow = editSht.Cells(editSht.Rows.count, editColumn).End(xlUp).Row
'this is the range of the data that we're looking for
Set dataRange = dataSht.Range(dataSht.Cells(1, dataColumn), dataSht.Cells(dataEndRow, dataColumn))
Dim count As Long
Dim val As String
For i = 1 To editEndRow
val = editSht.Cells(i, editColumn).Value
count = Application.WorksheetFunction.CountIf(dataRange, val)
If count > 0 And Trim(val) <> "" Then
editSht.Cells(i, editColumn).Value = replaceValue
End If
Next i
End Sub
You can use wildcards with find/replace. So in your situation you should be able to use something like
您可以在查找/替换中使用通配符。所以在你的情况下，你应该能够使用类似的东西
domain*in the find whatField and nothing in the Replacefield
domain*在find what场上，在Replace场上什么都没有
