syntax error in a vba relative reference formula

[Skrej]

Hi all,

Thanks to some research on other posts, I was able to get VBA to post this relative reference, which sticks the formula =DATE(YEAR($D2),MONTH($D2)+6, DAY($D2)+1) into a cell in Col J. All fine and dandy, works as it should with this formula.

.Cells(Row, "J").FormulaR1C1 = "=DATE(YEAR(RC4),MONTH(RC4)+6, DAY(RC4)+1)"

However, when I try this with a different formula =IF(ISNUMBER(SEARCH(C2,H2)),"REPEAT","SAFE"), I'm getting a compile error: Expected end of statement with this code:

.Cells(Row, "I").FormulaR1C1 = "=IF(ISNUMBER(SEARCH(RC3,RC8)),"REPEAT","SAFE")"

The error message highlights the word REPEAT. I suppose it's something with all the quotes throwing off where compiler thinks the end of the formula should be.

I'd appreciate any help on the correct syntax.

 

[Jonmo1]

If the formula you're entering with VBA contains quote marks, you have to double up those quotes.

.Cells(Row, "I").FormulaR1C1 = "=IF(ISNUMBER(SEARCH(RC3,RC8)),""REPEAT"",""SAFE"")"

 

[Skrej]

If the formula you're entering with VBA contains quote marks, you have to double up those quotes.

.Cells(Row, "I").FormulaR1C1 = "=IF(ISNUMBER(SEARCH(RC3,RC8)),""REPEAT"",""SAFE"")"

Yup, that did it. Thank you for the correction!

 

[Jonmo1]

Glad to help, thanks for the feedback.