Code To Open Files Within Folder Make Changes And Close

[Dazzawm]

I need a code that will open dozens of files within a folder in my desktop and if what is found in column A in the active sheet in column AG in each file then it needs to be changed to what is in column B as below. Thanks

Excel 2010

	A	B
1	Column AG	Changes To
2	Centre	Centre
3	Centre-LH	LH Centre
4	Centre-RH	RH Centre
5	FR	FR
6	FR Inner-LH	FR LH Inner
7	FR Inner-RH	FR RH Inner
8	FR Lower-LH	FR LH Lower
9	FR Lower-RH	FR RH Lower
10	FR Outer-LH	FR LH Outer
11	FR Outer-RH	FR RH Outer
12	FR Upper	FR Upper
13	FR Upper-LH	FR LH Upper
14	FR Upper-RH	FR RH Upper
15	FR-DS	FR DS
16	FR-Inner	FR Inner
17	FR-LH	FR LH
18	FR-Outer	FR Outer
19	FR-PS	FR PS
20	FR-RH	FR RH
21	Inner	Inner
22	Inner-LH	LH Inner
23	Inner-RH	RH Inner
24	LH	LH
25	Outer	Outer
26	Outer-LH	LH Outer
27	Outer-RH	RH Outer
28	RH	RH
29	RR	RR
30	RR Inner-LH	RR LH Inner
31	RR Inner-RH	RR RH Inner
32	RR LH+RH	RR LH+RH
33	RR-Inner	RR Inner
34	RR-LH	RR LH
35	RR-Outer	RR Outer
36	RR-RH	RR RH

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

So looking at row 3 if Centre-LH is found in column AG of any of the files then it needs to change it to LH Centre, save and close each file and so on...

 

[Dazzawm]

I have tried it on 3 more of my large files and it worked perfect! Could there be something in the files that it dosen't work on thats stopping it? If there was it would error out wouldn't it? This could indicate something wrong on these files that I don't know about and I need to change.

 

[Snakehips]

Sounds like good news/bad news.

Short of sending me a link to a bad file in a PM so that I can see what's breaking it down, I have not a clue.

 

[Dazzawm]

The first part of the code worked well on most of my files, some I had to make a few changes to manually. For some reason the second part just won't work as needed. Would it be possible to do a code for the second part of the code separately? So in other words I have the list of words in column A it needs to look for in each file in AG. Any words it finds that is not in the list then that needs to be listed (just one of each) and the file name. It may work better with these large files without the first part of the code. I appreciate I have taken up a lot of your time and understand if you want to take it no further. Thanks.

 

[Snakehips]

If you didn't need the reporting of unmatched words then I would have suggested that the first part be done by looping through A & B and just doing a find and replace within AG of the second file. However that is irrelevant because you do want the reporting and because the reporting is of words that exist in the second file that are not in the found in the search list in A, the current approach is appropriate.
I don't see splitting the code into two parts being of any benefit. It would certainly double the run time as it would need two loops through the data in AG. In the first run A items in AG would be replaced with B Items. in the second run it would have to to look for the non-existence of B items in AG. So other than searching on B rather than A, I would not see the coding approach for a separate second part being any different from that being used now with the two parts combined so cannot imagine that it will help.

 

[Dazzawm]

If you didn't need the reporting of unmatched words then I would have suggested that the first part be done by looping through A & B and just doing a find and replace within AG of the second file. However that is irrelevant because you do want the reporting and because the reporting is of words that exist in the second file that are not in the found in the search list in A, the current approach is appropriate.
I don't see splitting the code into two parts being of any benefit. It would certainly double the run time as it would need two loops through the data in AG. In the first run A items in AG would be replaced with B Items. in the second run it would have to to look for the non-existence of B items in AG. So other than searching on B rather than A, I would not see the coding approach for a separate second part being any different from that being used now with the two parts combined so cannot imagine that it will help.

Ok, its just I have run the first part of the code and changed all data that needs to be changed in each file. The second part of the code is to let me know if there and anomalies left that shouldn't be in there and need to be changed, without having to open each file individually do a sort etc etc.

No problem though and thanks very much for your time and efforts.