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 — brettdj
– brettdj, Commented Nov 17, 2012 at 1:23

brettdj · Accepted Answer · 2012-11-17 01:37:08Z

1

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

edited Nov 17, 2012 at 1:37

answered Nov 17, 2012 at 1:20

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

abbyssic Over a year ago

you guys are awesome! thanks to you both. how would i make it not change the initial values of a1-a5?

BtM909 · Accepted Answer · 2012-11-17 01:22:16Z

1

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

Range("B1").value

answered Nov 17, 2012 at 1:22

BtM909

112 bronze badges

Collectives™ on Stack Overflow

excel - Find and Replace multiple text from cell values

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related