excel - 从单元格值中查找和替换多个文本

I have an excel sheet with templated data. I want to be able to replace occurrences of words in A1:A5 with what I type into B1:B5 for 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-B5 instead of the literal text "B1","B2", ..., "B5", etc

但是我想用B1-B5的内容替换As,而不是文字“B1”,“B2”,......,“B5”等等。

2 个解决方案

#1

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 cat with dog but avoid changing catscan to dogscan then build in a space check before or after the string)

如果你想只在一个单元格中替换整个单词(例如,如果你想用狗替换猫但是避免将catcan更改为dogcan,那么在字符串之前或之后构建空格检查)

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

#2

You would be able to reference to a cell (address) value with

您可以使用引用单元格(地址)值

Range("B1").value

#1