# Find and Replace

#### Harold R.

##### Board Regular
I have a wrong Formula in a workbook that contain 18 worksheets. The wrong formula is:
=SUM((D12<C12)+D12-C12+(F12<E12)+F12-E12+(I12<H12)+I12-H12)*24
Is there a easy way of using find and replace this formula in each worksheet with :=SUM((D12<C12)+D12-C12+(G12<E12)+G12-E12+(I12<H12)+I12-H12)*24
Of coure the row number will change because the formula is in at least 30 rows on each sheet?

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### texasalynn

##### Well-known Member
What are you looking to replace?

#### Harold R.

##### Board Regular
A formula that has the wrong row refference Sum A11<B11 should be SUM B11< C11

***

#### pgc01

##### MrExcel MVP

Hi Harold

When posting formulas be careful with the "< " sign because HTML tries to interpret it and makes a mess, like in your post. In that case use the code button in the post reply form before and after the formula.

Code:
``=SUM((D12<C12)+D12-C12+(F12<E12)+F12-E12+(I12<H12)+I12-H12)*24``

and you want to replace it with

Code:
``=SUM((D12<C12)+D12-C12+(G12<E12)+G12-E12+(I12<H12)+I12-H12)*24``

The problem here is that "the formula is in at least 30 rows on each sheet". This means different columns, different rows, etc.

In this case I suggest you work in R1C1 notation. Since your formula has only relative addresses it does not change when you move it around.
You can then select the worksheets and use a Find/Replace.

Hope this helps
PGC

#### Harold R.

##### Board Regular
What is R!C! Notation and how do I do it? I know I am showing my stupidity but I don't know. Thanks

#### pgc01

##### MrExcel MVP
Hi Harold

There's a lot to learn. We all know very little.

You can use in excel 2 reference styles: A1 and R1C1.

In A1 reference style (the one you are using in the formula) you refer to a cell using letters to indicate the column and numbers to indicate the row.

In R1C1 you use numbers for both the column and the row.

Your formula only uses relative addresses. In this case a formula using R1C1 notation does not change when copied.

Example.

You have in B3
=A1+3
You copy it to B4 and you have
=A2+3
Now copy the formula to C4, you have
=B2+3

Although all 3 formulas mean the same: "cell 2 rows up and one column left +3", due to the A1 notation it is always changing, both the row and the column.

Now in R1C1 reference style, if in cell R3C2 (B3) you write the equivalent to (=A1+3)
=R[-2]C[-1]+3
In this notation if you copy it down or right it will not change because it does not depend on the cell that you enter it. It always means: "cell 2 rows up and one column left +3"

So what I meant was: turn R1C1 reference style on, in Tools>Options>General and then, since the formula is always the same you can select the worksheets and use Find/Replace.

You just need to use the command once if you select all the sheets where the formula is.

I hope it was clear.
PGC

#### Harold R.

##### Board Regular
Yes that worked great thanks. I continue to be amazed by what you can do with Excel. Again thank you very much for your help. Replies
4
Views
93
Replies
5
Views
422
Replies
9
Views
88
Replies
1
Views
46
Replies
5
Views
114