Solved

Vlookup in VBA to search and replace

Posted on 2012-03-22
3
539 Views
Last Modified: 2012-03-26
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
0
Comment
Question by:mato01
3 Comments
 
LVL 8

Accepted Solution

by:
wchh earned 500 total points
Comment Utility
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
 

Author Comment

by:mato01
Comment Utility
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
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now