使用Excel VBA粘贴Excel公式(Excel格式)

Question

如何仅在Excel公式中插入或粘贴大型Excel公式。我的项目有大量的Excel桌子，可以使No。剩下的几天，与当前日期和时间有关。每当我从数据输入表格提交数据时，必须将公式插入列中。它会自动从当前表中的不同单元格中收集值并计算。
但是在这里，我在公式行中获得了"编译错误，预期的语句结束"，即双重偏差"。

我可以直接在Excel和Drage中写
或
我可以为上述计算编写VBA代码，但是由于我的项目要求，必须插入公式。

有什么方法可以插入公式？？？？我正在使用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

Answer 1

尝试下面的代码，它将与您要测试的基本公式一起使用。

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

关于您的"长"公式，下面的公式字符串通过：

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

注意："长"公式的最终版本已由yowe3k编辑 - 如果它不起作用，则怪我（即yowe3k）而不是shai。

Answer 2

您需要首先在字符串中添加双引号，从而首先从字符串中逃脱双引号-ISBLANK($G7),""""

然后使用这样的公式

Range("O7").Formula = "[Your formula with escaped double quotes]"