excel - Find and Replace multiple text from cell values

Question

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.

Right now I'm going off of this (and I don't know much 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

This builds upon an earlier question, [Excel - Find and replace multiple words](http://stackoverflow.com/questions/13263154/excel-find-and-replace-multiple-words/13263284#13263284) — brettdj, Nov 17 '12 at 01:23

brettdj · Accepted Answer · 2012-11-17T01:37:08.970

Rather than

.Replace "ZIPCODE", "B1", xlPart
use
.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)

use
.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

you guys are awesome! thanks to you both. how would i make it not change the initial values of a1-a5? — abbyssic, Nov 17 '12 at 01:25

score 1 · Answer 2 · answered Nov 17 '12 at 01:22

1

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

Range("B1").value

answered Nov 17 '12 at 01:22

BtM909

11
2

excel - Find and Replace multiple text from cell values

2 Answers2