Find / Replace

**JayUSA** · 01-13-2010, 10:52 PM

Hello Everyone,

I want to change every formula on a particular worksheet that includes the sub-expression "+$c$8".

I want to substitute "+$b$48" for "+$c$8" in all those formulas, without altering the rest of those formulas, most of which are different from each other.

I've tried to use Find/Replace, but it can only find the "+$c$8" sub-expression in the one particular row that happens to be selected at that time

Changing the Find/Replace option from "Search by rows" to "Search by columns" means that it can only find the sub-expression in one particular column.

I want to find and replace this sub-expression in every formula where it appears in this worksheet.

What to do?

Cheers, Jay

**collinjc** · 01-13-2010, 11:02 PM

Try highlighting all the cells you want it changed in or just highlight all the cells before doing the replace.

**Paul** · 01-13-2010, 11:04 PM

Hi Jay,

Unfortunately I don't experience the same thing (Excel 2007). I put the following formula into 60+ cells:

=$B$8+$C$8

I open the Replace dialog and for 'Find What' I enter: +$C$8
For 'Replace with' I enter: +$B$48

I click Options to expand the dialog, and 'Within' is set to "Sheet", 'Search' is "By Rows" and 'Look In' is "Formulas". Upon clicking Replace All, all 60+ formulas update appropriately.

**JayUSA** · 01-13-2010, 11:19 PM

Hi Paul,

You have precisely described what I do... and the result is that it only finds the expression in those cells that are in the same row as whatever cell is highlighted at the moment!

Bizarre!

And highlighting cells or even the whole worksheet makes no difference. Very inconvenient!

Jay