Simple Excel 2010 issue

This seems simple enough... This following code is attached to a button to clear some data on a spreadsheet...

    ActiveCell.FormulaR1C1 = "=DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))-1"

The formula works fine but when it runs in code, Excel replaces the cell with this...

(Note the single quotes that are added)

If I edit the formula and remove the single quotes it works as expected but Excel is adding the single quotes when it inserts the formula.

How can I have Excel insert the code as I have written it??

Who is Participating?
Harry LeeConnect With a Mentor Commented:
Or you can use

 ActiveCell.Formula = "=DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))-1"
Harry LeeCommented:

ActiveCell.FormulaR1C1 = "=DATE(YEAR(R3C6),MONTH(R3C6)+1,DAY(R3C6))-1"
barry houdiniConnect With a Mentor Commented:
You are using "FormulaR1C1" but the notation in the formula is using conventional cell references, for R1C1 you need to make the references relative to H3 so because F3 is 2 columns before H3 that would be

ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-2]),MONTH(RC[-2])+1,DAY(RC[-2]))-1"

regards, barry
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

DanielTAuthor Commented:
Thank you both.
I see that both methods work OK.

Is this simply because the "FormulaR1C1" property cannot accept conventional cell references?
DanielTAuthor Commented:
I like the last example best. Thx HarryHYLee!

I think I started this code with an Excel Macro - could have been migrated from Excel 2003 as well.
Harry LeeConnect With a Mentor Commented:

Yes, the difference between those two are cell reference format.

Activecell.Formula uses the A1 cell reference format, and
Activecell.FormulaR1C1 uses the R1C1 format. R? is the row #, and C? is the column #.

They are not compatible with each other, and will not accept the wrong type of cell references.
DanielTAuthor Commented:
Thanks again!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.