Solved

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

Posted on 2014-12-26
6
116 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
Comment Utility
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Accepted Solution

by:
daviddiebel earned 0 total points
Comment Utility
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
Comment Utility
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
Comment Utility
I found this on an internet search.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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,…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

763 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

9 Experts available now in Live!

Get 1:1 Help Now