Multiple Find and replace

Hi
Can anyone help me, I need to recode 30+ files the have the old account numbers and I need to replace them with new codes ie

Old New
123456 10_1240
256789 12_1130
789123 15_1122

I have an excel spreadsheet which contains some 800 accounts in the format above showing old account number in column A and the new account number in column B. How would I write the VBA code to search all files in a specified folder search all rows in column E and find/replace using my spreadsheet containing the mapping details as above?
or in the very least just be able to run a macro to replace all the old codes in an individual spreadsheet.

Thanks in advance for you help!
Sarah

Hi Sarah

Will your 30+ files contain any codes in column E which will not match up to the 800 codes listed in column A of your Old-New sheet? eg perhaps if not all codes are being replaced?

Also, what is the structure of your 30+ workbooks - do they only have one sheet in each workbook or do they have potentially more sheets. If this is the case, will the replace operation need to be performed on each sheet or just on one of them (in which case how do we determine which sheet)?

How is the data structured on each sheet? will there be a column heading in E1 say and then data from E2 downwards? If so, are there any blanks in between data in column E (eg so E2 has data, E3 is blank, E4 has data and so on)?

Thanks

Richard

Hey Dowie, I didn't write this code (http://www.eggheadcafe.com/conversat...eadid=33659777) but I tested it on your sample workbook against a test workbook and you may be able to make it work for you. I'm attaching your original file (renamed) that includes the code along with my test file.
You'll notice in this procedure

Please Login or Register  to view this content.

at this line of code

Please Login or Register  to view this content.

I've defined a range based on your worksheet. You may want to revise this to look at the last row instead of hard coding C282.

Another issue that may or may not be a problem is this code (I didn't play with it to see what problems it creates)

Please Login or Register  to view this content.

This code loops through all the worksheets in the open workbooks and compares the sheet names. You MAY be able to comment out this entire procedure. I don't know. As I said, I didn't test it.

Another point, both files need to be open in the same Excel session (I tried with two separate sessions and got an "out of range" error).

Please notice, in the test file, I didn't overwrite the existing account code, I placed the new code in the cell to the right. You'll need to reivise that. You could probably add a user input form to this to prompt you for the file name of the next file you wish to compare; in the attached, "Test.xls" is hard coded. If you don't get better solutions, I'm convinced we can make this work for you. If you need further assistance in making it work, let me know.

Hope this helps. J

Dowie, one other thing I failed to point out is that the old account codes are in the same column in your file and my test file. If this is not the case in the real world, you will need to tell the procedure where to look. Again, I've not addressed this but am fairly certain we can make it work.

J

Try this instead. My previous post didn't like non-matched items.

J

Hey Dowie, I meant to ask you earlier but forgot. Is this a "one time" conversion or is it something you'll need to do on a monthly basis to conform with "Corporate" reporting requirements.

If it's one time, what I've proposed will take a bit of "grunt" work but it's one time. If it's "Corporate" requirements and needs to be done monthly, then we will need to develop an interface that does this stuff automatically. I don't pretend to know how to do this but we'll learn. There are individuals on this site that can do wonderful things with a bit of code.

J

Good morning Sarah
Hope it works for you. Let me know how you make out.
J

I am curious as well, this project could help me with mine.

Hey Sarah
I don't know where you are on this project but I've had some time to play with it today (don't have a day job...retired). I've built an interface that asks for information and then performs the task. The procedures assume all workbooks are in the same folder (same path) as the "Master" sheet (your conversion table).

The button on the "Master" sheet will bring up a User Input Form that will ask for the "Name of the File to Convert" (the workbook), the Account Start Range (where do account numbers start in this sheet) and the "Name of the Sheet to be Converted".

I've attached several files that you can play with to test to your hearts content. I've beat up on it for several hours and things seem to work fine (in my environment). Play with it in yours (on test data of course) and see if it does what you want.

It's an interesting project. Brings back fond memories. I'm an old "Numbers man". Let me know how it goes. J

Sarah...key point and I forgot to mention it. Your "Target" workbook CANNOT have a sheet named "Sheet1" UNLESS "Sheet1" is the name of the sheet to be converted. If "Sheet1" is not to be converted, before running the procedure, change the name to anything you like (I sorta like Sarah...a beautiful name) and change it back when you are finished.
Sorry for the oversight. J