Help with variable inside formula

[jordanburch]

Hey guys i have the following

ActiveCell.FormulaR1C1 = _
"=+IF(OR(RC[-20]=""CO SAR_" & ThisWorkbook.Worksheets("Variables").Range("A7").Value & ".xlsx"", RC[-20]='" & ThisWorkbook.Worksheets("Variables").Range("A2").Value & " CO IPAC " & "(DSSN 3801)" & ".xlsx""),""X"","" "")"


something is wrong with this section as its the only section i changed. I cant figure out the syntax for inserting a variable into formulas

RC[-20]='" & ThisWorkbook.Worksheets("Variables").Range("A2").Value & " CO IPAC " & "(DSSN 3801)" & ".xlsx""),""X"","" "")"

error object not defined

Can you guys help me correct it?

Jordan

 

[Oaktree]

Your quotes are not right in the line above "error object not defined".

Instead of single quote then double quote, I think it should be three double quotes.

Try:

ActiveCell.FormulaR1C1 = _
"=+IF(OR(RC[-20]=""CO SAR_" & ThisWorkbook.Worksheets("Variables").Range("A7").Value & ".xlsx"", RC[-20]=""" & ThisWorkbook.Worksheets("Variables").Range("A2").Value & " CO IPAC " & "(DSSN 3801)" & ".xlsx""),""X"","" "")"

 

[jordanburch]

Your quotes are not right in the line above "error object not defined".

Instead of single quote then double quote, I think it should be three double quotes.

Try:

ActiveCell.FormulaR1C1 = _
"=+IF(OR(RC[-20]=""CO SAR_" & ThisWorkbook.Worksheets("Variables").Range("A7").Value & ".xlsx"", RC[-20]=""" & ThisWorkbook.Worksheets("Variables").Range("A2").Value & " CO IPAC " & "(DSSN 3801)" & ".xlsx""),""X"","" "")"

awesome thanks a bunch OAKTREE! Worked great!