Solved

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

Posted on 2014-12-26
6
117 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
  • 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

947 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

18 Experts available now in Live!

Get 1:1 Help Now