首页 文章

使用excel VBA在单元格中粘贴excel公式（excel格式）？

提问于
浏览 次
0

如何仅在excel公式格式化中的特定单元格中插入或粘贴大型excel公式 . 我的项目有很大的excel表来表示没有 . 剩余的天数，它与当前的日期和时间相关联 . 每当我从数据输入表单提交数据时，都必须在列中插入公式 . 它会自动从当前工作表中的不同单元格中收集值并进行计算 .
但是在这里我得到"compile error, expected end of statement"在公式线即...，双quatotions“” .

我可以直接用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
excel excel-vba excel-formula vba

2 回答

  • 0

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

    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

    关于“LONG”公式，下面的公式字符串通过：

    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 .

    回复于
  • 0

    你需要首先通过在字符串中添加两次双引号来从字符串中转义双引号 - ISBLANK($G7),""""

    然后使用这样的公式

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

相关问题