Converting old code to new code

We have recently moved on to a new accounting system. We were forced to use a new coding system. The problem is that I used to download a Trial Balance in the old system and link an amount in column C that is in line with an account number in column A. The amounts on column C are ranged name with the account number in column A for eg if A1 is account 7650 the amount in column C will be named 7650 etc.

I would like VBA code that will :

1) Convert the old code in Column A in Sheet Man Accounts" to the new code (I will have a seperate worksheet named "Mapping" where I will have the old code in column A and the new code corresponding to the old code in column B


2) Any old code in a formula (I've use ranged names, with account numbers for eg if the account number in A1 is 7650 and the value is in C1 , then I've named C1 "7650") must be converted so that the new range name is picked up in the formula.

If there is an easier way to do this I would appreciate your comments

Attached please find a sample of my worksheets

Thanks

Howardhttp://www.mrexcel.com/board2/viewto...429&highlight=