Paste excel formula (excel format) in a cell using excel VBA?

Question

How do I insert or paste the large excel formula in a specific cell in excel formula formate only. My project has large excel table to calulate no. days left and it is linked with current date and time. formula has to be inserted in column whenever i submitted the data from data entry form. It automatically collects the values from different cells in a current sheet and calculates.
But here i getting "compile error, expected end of statement" at formula line ie., at double quatotions "".

I could write directly in excel and drage
or
I could write vba code for the above calculation but due to my project requirement, i have to be inserted the formula.

Is there any way to insert the formula???? i'm using excel 2016

Set Fcell = formulaWks.Range("O7")

'formula = "=$A1+$B1"  ' example for testing
Formula = "=IF(YEAR(NOW())=$W$3,IF(ISBLANK($G7),"",IFERROR(IF(DATEDIF(TODAY(),$N7,"y")=0,"",DATEDIF(TODAY(),$N7,"y")&" y ")&IF(DATEDIF(TODAY(),$N7,"ym")=0,"",DATEDIF(TODAY(),$N7,"ym")&" m ")&IF(DATEDIF(TODAY(),$N7,"md")=0,"",DATEDIF(TODAY(),$N7,"md")&" d"),"wrong date")),"Package completed")"
Fcell = ActiveCell.formula

what is "y", "ym" , "m", and "md" in your formula ? are they Integer ? Long variables defined ? — Shai Rado
"y", "ym" , "m", and "md" are the notations for the 'years' 'months' — suresh kumar

Shai Rado Shai Rado · Accepted Answer · 2016-12-04T06:52:21

Try the code below, it will work with your basic formula that you wanted to test.

Option Explicit

Sub InsertFormula()

Dim formulaWks As Worksheet
Dim Fcell As Range
Dim FormulaString   As String

' modify "Sheet1" to your sheet's name     
Set formulaWks = Worksheets("Sheet1")
Set Fcell = formulaWks.Range("O7")

FormulaString = "=$A1+$B1"  ' example for testing
Fcell.Formula = FormulaString

End Sub

Regarding your "LONG" formula, the formula string below passes:

FormulaString = "=IF(YEAR(NOW())=$W$3,IF(ISBLANK($G7)," & Chr(34) & Chr(34) & ",IFERROR(IF(DATEDIF(TODAY(),$N7," & Chr(34) & "y" & Chr(34) & ")=0," & Chr(34) & Chr(34) & ",DATEDIF(TODAY(),$N7," & Chr(34) & "y" & Chr(34) & ")&" & Chr(34) & " y " & Chr(34) & ")" & _
                "&IF(DATEDIF(TODAY(),$N7," & Chr(34) & "ym" & Chr(34) & ")=0," & Chr(34) & Chr(34) & ",DATEDIF(TODAY(),$N7," & Chr(34) & "ym" & Chr(34) & ")&" & Chr(34) & " m " & Chr(34) & ")" & _
                "&IF(DATEDIF(TODAY(),$N7," & Chr(34) & "md" & Chr(34) & ")=0," & Chr(34) & Chr(34) & ",DATEDIF(TODAY(),$N7," & Chr(34) & "md" & Chr(34) & ")&" & Chr(34) & " d" & Chr(34) & ")," & _
                Chr(34) & "wrong date" & Chr(34) & "))," & Chr(34) & "Package completed" & Chr(34) & ")" 

Debug.Print FormulaString ' for debug, to see the Formula string in the immediate window

Note: Final version of the "long" formula has been edited in by YowE3K - if it doesn't work, blame me (i.e. YowE3K) not Shai.

Paste excel formula (excel format) in a cell using excel VBA?

2 Answers