Solved

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

Posted on 2014-12-26
6
122 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

751 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