Vlookup in VBA to search and replace

I am currently using visual basic to search and replace text in a spreadsheet that I receive on a weekly basis.  For example if the data in Column A is Red, and I need to replace it with Candy Red, I use the below VBA.  Is there a way I could have the options in a table, and then have the VBA call out to lookup in the Possible Table, and then replace it with the correct text?
Trying not to have to do a vlookup everytime I get the file. Any thoughts would be appreciated.


Possible Table

COLOR      REAL COLOR
RED      CANDY RED
GREEN      FOREST GREEN
YELLOW      BANANA YELLOW
PURPLE      GRAPE PURPLE

Currently using the below vba

Sub Step7_ReplaceGroup
This macro converts the group names to the standard names
'
    Cells.Replace what:="Red", Replacement:="Candy Red", lookat:=xlPart _
        , searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="Green", Replacement:="Forest Green", lookat:=xlPart _
        , searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="Yellow", Replacement:="Banana Yellow", lookat:=xlPart _
        , searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="Purple", Replacement:="Grape Purple", lookat:=xlPart _
        , searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub
mato01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wchhCommented:
Try macro below:
Dim rng As Range
Dim cel As Range
'Range for table
Set rng = Sheets("Search").Range("A2:A5") 
For Each cel In rng
    Cells.Replace what:=cel, Replacement:=cel.Offset(0, 1), lookat:=xlPart _
        , searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Next cel

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mato01Author Commented:
I am currently using visual basic to search and replace text in a spreadsheet that I receive on a weekly basis.  For example if the data in Column A is Red, and I need to replace it with Candy Red, I use the below VBA.  Is there a way I could have the options in a table, and then have the VBA lookup and change the values in Table One, from the values in Table Two.


I need to clarify the situation.


Table One

COLOR      REAL COLOR

RED             CANDY RED
GREEN        FOREST GREEN
YELLOW     BANANA YELLOW
PURPLE      GRAPE PURPLE


Table Two

COLOR    
RED      
GREEN      
YELLOW      
PURPLE      

Table Two after the lookup

COLOR    
CANDY RED
FOREST GREEN
BANANA YELLOW
GRAPE PURPLE




Currently using the below vba

Sub Step7_ReplaceGroup
This macro converts the group names to the standard names
'
    Cells.Replace what:="Red", Replacement:="Candy Red", lookat:=xlPart _
        , searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="Green", Replacement:="Forest Green", lookat:=xlPart _
        , searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="Yellow", Replacement:="Banana Yellow", lookat:=xlPart _
        , searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="Purple", Replacement:="Grape Purple", lookat:=xlPart _
        , searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub
0
Rob HensonFinance AnalystCommented:
How many variables do you have?

You can use a couple of options that I can think of:

Select Case

CHOOSE using MATCH to find the original value in an existing array, effectively a lookup but finding the value in a one column array.

Thanks
Rob H
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.