Automatically Adding a IF Statement To A Column Of Existing Formula's

[GEANZ]

Hi Forum,

I have a column of different formulas 1500 rows long. No formulas are the same.

But I need to add the same IF statement to the start of them all.

Example 1,

Cell C2 has an existing formula:

"=IF(HeavyShells,1,IF(CombShells,0.5,0))*CupsPerCluster*NumofClusters*OR(ClawIQCutOff,ClawIQNoCutOff)"

This would become:

"=IF(I2<>"",IF(I2<>0,I2,IF(HeavyShells,1,IF(CombShells,0.5,0))*CupsPerCluster*NumofClusters*OR(ClawIQCutOff,ClawIQNoCutOff)))"




Example 2,

Cell C3 has an existing formula:

"=IF(OR(AND(SUM($C$5:$C$4)>0,SUM($C$18:$C$19)=0),LinerType=A17),NumofClusters*CupsPerCluster,0)*OR(ClawIQCutOff,ClawIQNoCutOff)"

This would become:

"=IF(I3<>"",IF(I3<>0,I3,IF(OR(AND(SUM($C$5:$C$4)>0,SUM($C$18:$C$19)=0),LinerType=A17),NumofClusters*CupsPerCluster,0)*OR(ClawIQCutOff,ClawIQNoCutOff)))"



I don't want to have to add the IF statement manually to all 1500 lines!

Many thanks in advance.

-Ollie

[Debraj Roy]

Try this..

Please Login or Register  to view this content.

I hope there was no array formula in all the formulas.. crossing finger..

[GEANZ]

Hi thanks for the Reply, that's the right Idea but it's not dynamic.

The IF formula (for C2) is looking at I2 then for C3 its looking at I3, the above keeps referring to I3 each time.

Does the code need some sort of loop that adds a row each time?

Also maybe I should run a VBA test of some sorts to detect if any of the formulas in the Column are Arrays? Is there a way to do that!?

[GEANZ]

Solved this with the following: