0

I want to add a formula to a cell via VBA, but for some reason fail to get it done.

I've tried

ws.Range("O2").Formula = "=DATEDIF($N$2;C1+1;""m"")/3+1"

Error message is 1004 ("Application-defined or object-defined error")

ws.Range("O2").Formula = "=DATEDIF($N$2;C1+1;" & Char(34) & "m" & Char(34) & ")/3+1"

Error message here is "Sub or function not defined"

How do I do this?

Alex
  • 515
  • 5
  • 19
  • Try Chr(34) ... – Naresh May 20 '20 at 12:03
  • 1
    Try `FormulaLocal` – Ron Rosenfeld May 20 '20 at 12:08
  • If that doesn't work, enter the formula manually on the worksheet; make sure it works; then examine the `Range.Formula` property of the cell in which you placed it. – Ron Rosenfeld May 20 '20 at 12:11
  • 1
    Use comma(,) in place of semicolon (;) `ws.Range("O2").Formula = "=DATEDIF($N$2,C1+1," & Char(34) & "m" & Char(34) & ")/3+1"`. This has been discussed [here](https://stackoverflow.com/questions/341258/write-a-formula-in-an-excel-cell-using-vba) before . – shrivallabha.redij May 20 '20 at 12:40
  • @shrivallabha.redij Thank you, somehow I missed this (despite making this mistake before). – Alex May 20 '20 at 12:44
  • VBA can be "us-centric", especially with regard to formulas. So you have to either use the `.FormulaLocal` property, or write using the US style formulas. – Ron Rosenfeld May 20 '20 at 12:54

0 Answers0