FIND and REPLACE

I have a problem in which I must select a range and do a find and replace on the formula string ONLY, not the cell value. I am using Excel 2010 on a XP machine.

As an example:

A B C
1 a1 .. a1
2 .. .. ..
3 99 .. ..

Cell A1 is the value 'A1', C1 is a formula '=A1', A3 is a value '99'.

What I want to do is select the range A1 thru C3, run a FIND & REPLACE (or equivelant), and in the box replace '1' with '3', thereby making the formula in C3 '=A3' and changing it's value as well but leaving the cell value in A1 alone.

So that afterwards it would look like this.

A B C
1 a1 .. 99
2 .. .. ..
3 99 .. ..

Find and replace claims to look at formulas but it also looks at cells without formulas as well.

Is there any solution to this?

Highlight A1:C3 -> press F5 -> Special -> select Formulas -> OK -> now run Edit & Replace

Thanks for replying. Doesn't work for me tho in 2010, if I do the steps and say 'replace '1' with '2', then cell A1's value changes to A2 (bad) and cell C1 formula changes to =A2 (good), cell value 0 (from A2).

What I need is for only the formula to change, and not the cell value.

All the formulas are '=(cell location)...perhaps something to do with making only formula cells visible and edit/replace special on visible would work??

Nope...didn't seem to work, F & R worked on the invisible cell too..

Originally Posted by KWMKWM

Doesn't work for me tho in 2010

I assumed you would have > 1 formulae in your "real" sheet.

If you don't - add a dummy formula (doesn't matter what it is), include that cell in your initial selection - then repeat the steps outlined.

If you're applying to a range with > 1 formula and it's still not working post an example that demonstrates it not working.
I confess I've done only limited testing...