?
Solved

Vlookup in VBA to search and replace

Posted on 2012-03-22
3
Medium Priority
?
551 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 8

Accepted Solution

by:
wchh earned 2000 total points
ID: 37751364
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
ID: 37755626
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 33

Expert Comment

by:Rob Henson
ID: 37756887
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

770 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