Solved

I would like a macro to perform many find and replace actions using a specified list.

Posted on 2014-12-26
6
121 Views
Last Modified: 2014-12-31
Please see the attached workbook.  I have a huge number of find/replace actions to perform.  I will always be replacing numbers with other sets of numbers.  I would like to paste the data on which to perform the find/replace action in the "Data" worksheet, and list the old values being replaced and the corresponding new values in columns A and B in the "Old and New Values" worksheet - I only want the find/replace actions to be performed on the "Data" worksheet.

I don't need a copy of the old data to be preserved.  Sometimes the number strings might be in brackets or parentheses - I want to preserve those, and just replace the numbers.
0
Comment
Question by:daviddiebel
[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
  • 4
6 Comments
 

Author Comment

by:daviddiebel
ID: 40518990
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40519004
If indeed this is your exclusive list of values to replace, you could just do a straight search and replace like so:
search and replace
This would work regardless of the occurrence of other characters (like brackets).

If, however, your replacement list is more complex than this (i.e., substrings being mutually exclusive), then a macro would be needed.

Regards,
-Glenn
0
 

Author Comment

by:daviddiebel
ID: 40519021
I want to avoid having to enter many hundreds of numbers into the Find and Replace box, and this will be an ongoing task.  That is an example list and not an exclusive one.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Accepted Solution

by:
daviddiebel earned 0 total points
ID: 40519170
I figured it out!

Sub multiFindandReplace()
    Dim myList, myRange
    Set myList = Sheets("Old and New Values").Range("A2:B1000")
    Set myRange = Sheets("Data").Range("A1:DN65000")
    For Each cel In myList.Columns(1).Cells
        myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWhole
    Next cel
End Sub
0
 
LVL 10

Expert Comment

by:broro183
ID: 40520101
hi David,

If the number strings might be in brackets or parentheses you may need to change "LookAt:=xlWhole" to "LookAt:=xlPart". If this is the case, you may need to be more wary about the order that the substrings are replaced as Glenn has mentioned.

hth
Rob
0
 

Author Closing Comment

by:daviddiebel
ID: 40525161
I found this on an internet search.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

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,…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

740 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