vba excel - 从单元格值中查找和替换多个文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13426671/
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
excel - Find and Replace multiple text from cell values
提问by abbyssic
I have an excel sheet with templated data. I want to be able to replace occurrences of words in A1:A5with what I type into B1:B5for the rest of the document.
我有一个带有模板数据的 Excel 工作表。我希望能够A1:A5用我B1:B5为文档的其余部分键入的内容替换出现的单词。
Right now I'm going off of this (and I don't know much VBA):
现在我要离开这个（我知道的不多VBA）：
Sub UpdatePartial()
With ActiveSheet.UsedRange
.Replace "ZIPCODE", "B1", xlPart
.Replace "NAME", "B2", xlPart
.Replace "CITY", "B3", xlPart
.Replace "STATE", "B4", xlPart
.Replace "PHONE", "B5", xlPart
End With
End Sub
but I would like to replace the As with the contents of B1-B5instead of the literal text "B1","B2", ..., "B5", etc
但我想A用内容替换sB1-B5而不是文字文本"B1","B2", ..., "B5"等
采纳答案by brettdj
Rather than
而不是
.Replace "ZIPCODE", "B1", xlPart
use.Replace [a1].Value, [b1].Value, xlPart
.Replace "ZIPCODE", "B1", xlPart
用.Replace [a1].Value, [b1].Value, xlPart
If you want to replace whole words only within a cell (ie if you wanted to replace catwith dogbut avoid changing catscanto dogscanthen build in a space check before or after the string)
如果您只想在一个单元格中替换整个单词（即，如果您想用dog替换cat但避免将catscan更改为dogscan然后在字符串之前或之后建立一个空格检查）
use.Replace " " & [a1].Value, " " & [b1].Value, xlPart.Replace [a1].Value & " ", [b1].Value & " ", xlPart
用.Replace " " & [a1].Value, " " & [b1].Value, xlPart.Replace [a1].Value & " ", [b1].Value & " ", xlPart
Your updated question
你更新的问题
Sub UpdatePartial()
Dim rng1 As Range
ActiveSheet.UsedRange
Set rng1 = Intersect(ActiveSheet.UsedRange, Range(Cells(6, "a"), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count)))
With rng1
.Replace [a1].Value, [b1].Value, xlPart
End With
End Sub
回答by BtM909
You would be able to reference to a cell (address) value with
您将能够引用单元格（地址）值
Range("B1").value
