超长公式如何用VBA写入表格

alanlad

各位老师好

我有个公式想插入表格的H2单元格，但是录制宏的时候发现公式太长，代码框写不下，我用&_连字符换行也不行，麻烦老师指导下如何分段写。公式如下：
公式如下：=INT((SUMIFS(生产领料序时簿!K:K,生产领料序时簿!M:M,IF(MONTH(TODAY())-1=0,YEAR(TODAY())-1&"-12",YEAR(TODAY())&"-"&MONTH(TODAY())-1),生产领料序时簿!H:H,B2)+SUMIFS(生产领料序时簿!K:K,生产领料序时簿!M:M,IF(MONTH(TODAY())-2=0,YEAR(TODAY())-1&"-12",IF(MONTH(TODAY())-2=-1,YEAR(TODAY())-1&"-11",YEAR(TODAY())&"-"&MONTH(TODAY())-2)),生产领料序时簿!H:H,B2)+SUMIFS(生产领料序时簿!K:K,生产领料序时簿!M:M,IF(MONTH(TODAY())-3=0,YEAR(TODAY())-1&"-12",IF(MONTH(TODAY())-3=-1,YEAR(TODAY())-1&"-11",IF(MONTH(TODAY())-3=-2,YEAR(TODAY())-1&"-10",YEAR(TODAY())&"-"&MONTH(TODAY())-3))),生产领料序时簿!H:H,B2)+SUMIFS(生产领料序时簿!K:K,生产领料序时簿!M:M,IF(MONTH(TODAY())-4=0,YEAR(TODAY())-1&"-12",IF(MONTH(TODAY())-4=-1,YEAR(TODAY())-1&"-11",IF(MONTH(TODAY())-4=-2,YEAR(TODAY())-1&"-10",IF(MONTH(TODAY())-4=-3,YEAR(TODAY())-1&"-9",YEAR(TODAY())&"-"&MONTH(TODAY())-4)))),生产领料序时簿!H:H,B2)+SUMIFS(生产领料序时簿!K:K,生产领料序时簿!M:M,IF(MONTH(TODAY())-5=0,YEAR(TODAY())-1&"-12",IF(MONTH(TODAY())-5=-1,YEAR(TODAY())-1&"-11",IF(MONTH(TODAY())-5=-2,YEAR(TODAY())-1&"-10",IF(MONTH(TODAY())-5=-3,YEAR(TODAY())-1&"-9",IF(MONTH(TODAY())-5=-4,YEAR(TODAY())-1&"-8",YEAR(TODAY())&"-"&MONTH(TODAY())-5))))),生产领料序时簿!H:H,B2)+SUMIFS(生产领料序时簿!K:K,生产领料序时簿!M:M,IF(MONTH(TODAY())-6=0,YEAR(TODAY())-1&"-12",IF(MONTH(TODAY())-6=-1,YEAR(TODAY())-1&"-11",IF(MONTH(TODAY())-6=-2,YEAR(TODAY())-1&"-10",IF(MONTH(TODAY())-6=-3,YEAR(TODAY())-1&"-9",IF(MONTH(TODAY())-6=-4,YEAR(TODAY())-1&"-8",IF(MONTH(TODAY())-6=-5,YEAR(TODAY())-1&"-7",YEAR(TODAY())&"-"&MONTH(TODAY())-6)))))),生产领料序时簿!H:H,B2))/6)

feilanga

     Range("c16") = "=SUMIF(A2:A6,""b"",B2:B6)" 遇到单引号就把单引号加倍
但是使用公式容易卡顿

morpheus126

如果没记错的话，公式不能超过255个字符，VBA也得遵守基本法
真要那么长，用名称嵌套吧

ykcbf1100

用VBA的话，就不要用那么长的公式了，没必要。

考试加油站

没附件不好测试

Sub InsertFormula()
    Dim ws As Worksheet
    Dim formulaPart1 As String
    Dim formulaPart2 As String
    Dim formulaPart3 As String
    Dim formulaPart4 As String
    Dim formulaPart5 As String
    Dim formulaPart6 As String
    Dim finalFormula As String

    Set ws = ThisWorkbook.Worksheets("Sheet1") '请根据实际情况修改工作表名称

    formulaPart1 = "=INT((SUMIFS(生产领料序时簿！K:K,生产领料序时簿！M:M,"
    formulaPart2 = "IF(MONTH(TODAY())-1=0,YEAR(TODAY())-1&""-12"",YEAR(TODAY())&""-""&MONTH(TODAY())-1),生产领料序时簿！H:H,B2)+"
    formulaPart3 = "SUMIFS(生产领料序时簿！K:K,生产领料序时簿！M:M,"
    formulaPart4 = "IF(MONTH(TODAY())-2=0,YEAR(TODAY())-1&""-12"",IF(MONTH(TODAY())-2=-1,YEAR(TODAY())-1&""-11"",YEAR(TODAY())&""-""&MONTH(TODAY())-2)),生产领料序时簿！H:H,B2)+"
    formulaPart5 = "SUMIFS(生产领料序时簿！K:K,生产领料序时簿！M:M,"
    formulaPart6 = "IF(MONTH(TODAY())-3=0,YEAR(TODAY())-1&""-12"",IF(MONTH(TODAY())-3=-1,YEAR(TODAY())-1&""-11"",IF(MONTH(TODAY())-3=-2,YEAR(TODAY())-1&""-10"",YEAR(TODAY())&""-""&MONTH(TODAY())-3))),生产领料序时簿！H:H,B2)+"
    finalFormula = formulaPart1 & formulaPart2 & formulaPart3 & formulaPart4 & formulaPart5 & formulaPart6 & "))/6)"

    ws.Range("H2").Formula = finalFormula
End Sub