Automatically Replace Text Based Off A List

Question

Is there any way to have a spreadsheet in Excel automatically update text based off a list on a separate sheet?

So, let's say every time I copy content into Sheet1 and would like it to automatically update the words Apple, Orange and Banana to their corresponding words on a list in Sheet2, what would be the best way?

I have a extensive list of branch codes which are assigned to their branch names but the data extracted from our system only provides the branch codes. Which is why I am trying to find a way to have this updated by default.

In the example below, I would like the content in the column Branch, which is in Sheet1, to be automatically replaced to what my list provides in another sheet, which is the second image below.

Content in Sheet1:

And Sheet2 with the list:

Thanks for your help!

Yes, absolutely this can be done. What have you already tried? — Chris Melville
– Chris Melville, Commented Feb 13, 2018 at 0:02
Please include some sample data (before and after) which at least closely matches your actual data (if it's not confidential/sensitive then post the actual data) — Tim Williams
– Tim Williams, Commented Feb 13, 2018 at 0:05
@TimWilliams Of course. Just a sec. I will add two screenshots. — Francis
– Francis, Commented Feb 13, 2018 at 0:09
@ChrisMelville I wasn't quite sure how to approach this. I know how to enter formulas into the individual cells to replace texts, but don't know how to create something that searches through an entire sheet and automatically replaces something based on a list. — Francis
– Francis, Commented Feb 13, 2018 at 0:13
If you're asking specifically for a VBA solution without posting any of your own attempts, then people here are generally inclined to downvote/vote to close. — Tim Williams
– Tim Williams, Commented Feb 13, 2018 at 0:44

Francis · Accepted Answer · 2018-02-13 15:36:04Z

1

After spending some more time searching for help, I came across this code which did the job for me:

Sub UpdateBranchNames()
    Dim r As Range
    With Sheets("Sheet2")
        For Each r In .Range("A1:A500", .Range("a" & Rows.Count).End(xlUp))
            Sheets("Sheet1").Columns(8).Replace r.Value, r(, 2).Value
        Next
    End With
End Sub

Thanks for the responses.

UPDATE:

Here is an updated version thanks to @QHarr.

Sub UpdateBranchNames()
    Dim r As Range
    Dim myRange as Range
    Dim myList as Worksheet
    Set myList = Sheets("Sheet2")
    Set myRange = myList.Range("A1:A500", myList.Range("a" & Rows.Count).End(xlUp))

    With myList
        For Each r In myRange
            Sheets("Sheet1").Columns(8).Replace r.Value, r(, 2).Value
        Next
    End With
End Sub

edited Feb 13, 2018 at 15:36

answered Feb 13, 2018 at 2:10

Francis

1292 silver badges12 bronze badges

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

2 Comments

QHarr Over a year ago

Put this .Range("A1:A500", .Range("a" & Rows.Count).End(xlUp)) into a variable e.g. Dim myRange As Range. Set myRange = Sheets("Sheet2") .Range("A1:A500", .Range("a" & Rows.Count).End(xlUp)) Also, consider putting Sheets("Sheet2") into a variable. It will be more efficient.

Francis Over a year ago

@QHarr Thanks for your help! I have added your suggestion to the answer above.

Collectives™ on Stack Overflow

Automatically Replace Text Based Off A List

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related