Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-12-26
6
Medium Priority
?
131 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
 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

572 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