Trying to insert formula into one cell if another cell contains certain text

[davcurnutt]

I am trying to us VBA to input a formula in column O if column A contains the text "Cancel Details". If it contains the text, I want a formula inserted in Column O that will multiply column I and J and add column M. There are multiple worksheets in the workbook and I want it to do this to all sheets. This is what I put in VBA but doesn't work.

Range("O1:O900").Formula = _
"=IF(($A1="Cancel Details",=($I1*$J1)+$M1))"

 

[Joe4]

Welcome to the Board!

The main issue is with the double-quotes, as they are used as text delimiters in both your formula and in VBA.
But you also have some others, like get rid of the "=" in the middle of your formula. And you have not specificed what to do when column A is NOT "Cancel Details" (currently, it would return "FALSE").

Here is a little "hack" to solve the issue of how the formula needs to be written for VBA.
Once you have a valid, workinf formula, turn on your Macro Recorder, and record yourself entering the formula into cell O1.
Then stop the Macro Recorder and view the code you recorded. You can then copy the formula part to your code, and this will ensure that you have all the necessary double-quotes for a valid formula.

 

[ExceLoki]

try this

Sub test()
    Range("O1:O900").Formula = "=IF($A1=""Cancel Details"",($I1*$J1)+$M1)"
End Sub

 

[Joe4]

ExcelLoki,

If you are going to use "R1C1" format, then you need to change

.Formula

to

.FormulaR1C1

 

[ExceLoki]

already updated it, saw as soon as i posted it

 

[davcurnutt]

I tried this but I get an Invalid Outside Procedure error
Sub CancelFormula()
'
' CancelFormula Macro
'

'
Range("O1:O900").FormulaR1C1 = "=IF($A1=""Cancel Details:"",(($I1*$J1)+$M1),("" ""))"
End Sub

 

[davcurnutt]

try this

Sub test()
    Range("O1:O900").Formula = "=IF($A1=""Cancel Details"",($I1*$J1)+$M1)"
End Sub

I tried this but I get an Invalid Outside Procedure error
Sub CancelFormula()
'
' CancelFormula Macro
'

'
Range("O1:O900").FormulaR1C1 = "=IF($A1=""Cancel Details:"",(($I1*$J1)+$M1),("" ""))"
End Sub

 

[Joe4]

Did you try ExceLoki's code?

 

[davcurnutt]

Yes, I get the same error. Not sure what I am doing wrong with this one.

 

[Joe4]

Yes, I get the same error. Not sure what I am doing wrong with this one.
View attachment 88791

That seems to suggest that you have some VBA code in your module that is not between the "Sub" and "End Sub" lines.

So it may have nothing to do with this code at all, and may be something else you got floating out there.
Can you post the all the code you have in that particular module, or is it too long?