Find and Replace Macro

[ls010s]

Hi

I need some help with a file I am working with.

On Sheet 1, I have data which is downloaded from a system with inconsistent data. I have a list of all the possible "find and replace" items I need to run in column B of sheet 1 to make it consistent.

If in sheet 2 column A and B, I copy all these find and replace items (i.e. find "-" and replace with "/" and so on), how can I set up a macro to look at sheet 2, column a and b and replace in sheet 1 in column B instead of me updating the code every time a new find and replace item is added?

The total list is currently at 90 and every time something changes I have to update the VBA code to ensure that the find and replace macro works which can be time consuming. I am using Office 2007.

Thanks for the help.

 

[ZAX]

Right click on Sheet2 tab and choose view code and paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
If Target.Offset(0, -1).Value = "" Then
   Exit Sub
Else
   Sheets("Sheet1").Range("B:B").Replace Target.Offset(0, -1).Value, Target.Value
End If
End Sub

Note:This will work if you put the value in column A first then the value in column B e.g: A5 = "/" without the qoutes"" and B5 = ","
ZAX

 

[ls010s]

Thanks a ton!

Works perfectly

 

[ZAX]

Thanks a ton!

Works perfectly

You're welcome!

 

[LadyAmpherion]

So, I've already posted a new question but it seems like mine could piggy back onto this solution ... I have the listing of items to Find and Replace on one tab.

But I need to search the complete workbook and I have several workbooks, so I'm thinking I build the Macro in the Workbook with the Find/Replace info and then run it on the other workbooks.

My problem ... I've never done a macro before. I 135 items to search on and a dozen workbooks. so I am trying to avoid 1620 find/replace sequences.

Any ideas?

Thanks in advance for your time and assistance.