Search and Replace Text with a formula and adjacent cell with a formula

Using 2010

I found this post, that does what i want to do in theory:

http://www.excelforum.com/excel-prog...cent-cell.html

And it works when I use text. When I use a formula it starts to change things but hangs and never returns.

I have a worksheet called key, that has two columns. I want to replace other worksheets with values from this column. For example, any cell that has the word Arizona should become =key!A2 and the cell to its right should become =key!B2. Here is the code that works fine until I use formulas. I'm hoping somebody could help me out, and maybe even make it run against all worksheets

Please Login or Register  to view this content.

Oddly enough, if the cell that i'm referencing is populated it appears to hang. If it is empty it works fine, then i fill in the reference cell....