syntax error in a vba relative reference formula

Skrej

Board Regular
Joined
May 31, 2013
Messages
135
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.

Code:
.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:

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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Board Regular
Joined
May 31, 2013
Messages
135
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,976
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top