syntax error in a vba relative reference formula

Skrej

Board Regular
Joined
May 31, 2013
Messages
139
Office Version
  1. 365
Platform
  1. Windows
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

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
139
Office Version
  1. 365
Platform
  1. Windows
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,467
Messages
5,853,920
Members
431,604
Latest member
ahmirof

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