Insert the same same if statement in many cells with different formulas

[brubru24]

Hi,

I have many cells each with different formulas for example:

A1=G14/(1-$J$1)
A2=G15/(1-$J$1)
A3=G16/(1-$J$1)
A4=G17/(1-$J$1)

What I would like to do is to quickly insert the same if statement or at least partially in each cell above without having to change the pre-existing formula like below:

A1=If(B3="Yes",G14/(1-$J$1),0)
A2=If(B3="Yes",G15/(1-$J$1),0)
A3=If(B3="Yes",G16/(1-$J$1),0)
A4=If(B3="Yes",G17/(1-$J$1),0)


I hope it makes sense and thank you in advance for your help.

Kind regards,

Bruno

 

[jasonb75]

Try it as

=If(B$3="Yes",G14/(1-$J$1),0)

In A1, then copy it to the rest of the cells.

 

[brubru24]

Thank you.

I should have been more specific in my original request. I didn't mention that each cell has a different not linear formula so for example:

=If(B3="Yes",G27/(1-$J$1),0)
=If(B3="Yes",X28/(1-$J$1),0)
=If(B3="Yes",G30,0)

Thank you.Bruno

 

[jasonb75]

This small piece of vba will apply the change to the selected cells, this is based on your examples, if you need variations then that will need to be allowed for.

Sub replaceformula()
Dim c As Range
For Each c In Selection
    c.Formula = "=IF(B$3=""Yes""," & Mid(c.Formula, 2, Len(c.Formula)) & ",0)"
Next
End Sub

 

[Peter_SSs]

Deleted - incorrect answer

 

[Peter_SSs]

This small piece of vba will apply the change to the selected cells, this is based on your examples, if you need variations then that will need to be allowed for.

Sub replaceformula()
Dim c As Range
For Each c In Selection
    c.Formula = "=IF(B$3=""Yes""," & Mid(c.Formula, 2, Len(c.Formula)) & ",0)"
Next
End Sub

Jason, note that in vba the third argument for mid is not required if you want the whole rest of the string. So the red part can be omitted here.

 

[jasonb75]

Jason, note that in vba the third argument for mid is not required if you want the whole rest of the string. So the red part can be omitted here.

Thanks Peter, I wasn't aware of that, I just use the same method as I would if it was a worksheet formula.

 

[Peter_SSs]

Thanks Peter, I wasn't aware of that, I just use the same method as I would if it was a worksheet formula.

No problem. I like this shortened option and wish the worksheet function worked this way as well.

 

[brubru24]

Both, thank you so much.