Find and Replace

Harold R.

Board Regular
Joined
Sep 26, 2004
Messages
132
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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Harold R.

Board Regular
Joined
Sep 26, 2004
Messages
132
A formula that has the wrong row refference Sum A11<B11 should be SUM B11< C11
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

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.

I believe your formula is

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
Joined
Sep 26, 2004
Messages
132
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
Joined
Apr 25, 2006
Messages
19,884
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
Joined
Sep 26, 2004
Messages
132
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. :biggrin:
 

Forum statistics

Threads
1,141,429
Messages
5,706,406
Members
421,447
Latest member
arthuro2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top