Solved

Vlookup in VBA to search and replace

Posted on 2012-03-22
3
547 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
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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 …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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.

749 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