Revisit: Replace Names within a sheet

[jomili]

We solved a thread here long ago, which I need to resurrect: http://www.excelforum.com/excel-prog...html?p=2875697
The whole idea was that in my BA column I have BAs, who relate to a certain Dept, and I have "Orphan Trans", which can be in any dept. For that column, if the cell says "Orphan Trans", I want to change it to whatever the BA's name for that Dept is, plus "-Orphan Trans".

JosephP posted a great solution, which I didn't fully understand at the time, and still don't, and now I need to update it and am having a miserable time. The source data has been changed: originally the "Orphans" were in column B, now in Column C. Depts was in Column L, now Column M. The range of data in my previous example extended to Column L; it actually goes to column BP. I've posted a new example.

Below is my attempted updated version (the original code is at the original post). It doesn't error anywhere, it just doesn't add the BA's name to the "orphan Trans." cell. Any help is greatly appreciated.

Please Login or Register  to view this content.