Hi, I'm working for a financial company that is updating their ledgering software, and part of that update includes new fund and account codes. The department I'm working in is the accounting department, and they use various workbooks and spreadsheets to track all the fund and account transactions and help with reconciliation. Many of these workbooks contain multiple sheets, so in total there could be about 100 worksheets used for various accounting purposes. Many of these sheets contain references to specific fund and account codes in their formulas. The two most common examples of references would be:

1) A cell that contains a comment that includes a fund/account code. For example, A1 might contain "Closing balance for A/C 350" and A2 might contain the actual closing balance.

2) A cell that contains a formula where the output of the formula is a fund/account code. For example, there might be two accounts - 121 and 145 - used to track oustanding balances for a fund, where if the balance is positive, the amount is posted to account 121, and if the balance is negative, the amount is posted to account 145. In this case, cell A1 might contain a formula that reads "=If(BALANCE<0,"145","121")".

Anyway, what I've been asked to do is start going through all the workbooks and spreadsheets used by this department and update the references throughout to fund or account codes. I have a cross-reference list of old fund/account codes and the new codes used in the updated software. I've created copies of all the original sheets and books for updating and testing, but I'm currently going through manually and using the Replace function. But that invovles me scanning each sheet to determine any references and replacing them.

What I'm wondering is if it's possible to write a macro that will open up the workbook I point it to, go through each sheet, and look for the references and replace them. Because the codes are numeric and it's possible that the particular sequence of numbers might occur in a cell containing financial data, when I'm manually using the Replace function, I don't just click on "Replace All" but check each reference it finds. Thus, I would also like to have this macro not automatically update every cell and formula it finds, but maybe pop up with a msg box allowing me to see what the cell and formula is and decide whether to update it or not.

Now, from what I know of macros and VB for excel, this should be all possible. It shouldn't be too hard to first ask the user which workbook to open, to then go through each sheet one by one, to go through all the cells in each sheet which contain data, and to look for references to any of the old fund/account codes. What about the msg box? If I write in the code to, upon finding a match, pop up a msg box with a Yes or No option, will I be able to see the cell with the potential match each time the msg box pops up? Or in the msg itself, can I display the cell's contents?

Also, does anyone have any tips/suggestions on particular functions that would help with any of this? I'm basically doing a search and replace, and I've done those before, but never on this scale involving multiple workbooks and worksheets and something like 300 search items. Thanks a ton in advance for the help!