Solved

Vlookup in VBA to search and replace

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

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

785 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