公式R1C1和"=SUMIFS("

Question

我正在尝试根据业务的BUcode和Division添加特定位置类型中占用位置的所有单元。

注意：
- 变量 binshelv 和被占用的在工作表中定义了名称。
- 变量division 和 bucode 在父代码中定义，该代码沿着值调用并传递到该过程。

以下是我要输入的代码：

ActiveCell.FormulaR1C1 = _
    "=IF(" & _
        "" & BUcode & "=Overall," & _
        "if(" & _
                "" & Division & "=Overall, " & _
                "SUMIFS(" & _
                        "DataDump[On Hand Qty (Units)]," & _
                        "DataDump[Occupied?], occupied," & _
                        "DataDump[Location Short Description], binshelv" & _
                ")," & _
                "Sumifs(" & _
                        "DataDump[On Hand Qty (Units)]," & _
                        "DataDump[Occupied?], occupied," & _
                        "DataDump[Location Short Description], binshelv," & _
                        "DataDump[Division Code], " & DivCode & "," & _
                ")" & _
        ")," & _
        "SUMIFS(" & _
                "DataDump[On Hand Qty (Units)]," & _
                "DataDump[Business Unit]," & BUcode & "," & _
                "DataDump[Location Short Description], binshelv" & _
        ")" & _
    ")"

为什么我当前得到Error '1004'？

Answer 1

似乎问题是您的公式不等于有效的字符串。如果您将以下代码复制到VBA编辑器中，您会发现strformula不等于有效的字符串。这意味着您在公式中有错误。要记住的一件事是，您需要在公式中加倍引用引号，或使用逃生字符在字符串中使用文字引号。

玩" strformula ="以等同于有效的字符串。

Sub aa()
    Dim strFormula As String
    Dim binshelv, occupied, Division, BUcode As String
    ' **--- Get this line to equate to a valid string**
    strFormula = _
    "=IF(" & _
    "" & BUcode & "=Overall," & _
    "if(" & _
            "" & Division & "=Overall, " & _
            "SUMIFS(" & _
                    "DataDump[On Hand Qty (Units)]," & _
                    "DataDump[Occupied?], occupied," & _
                    "DataDump[Location Short Description], binshelv" & _
            ")," & _
            "Sumifs(" & _
                    "DataDump[On Hand Qty (Units)]," & _
                    "DataDump[Occupied?], occupied," & _
                    "DataDump[Location Short Description], binshelv," & _
                    "DataDump[Division Code], " & DivCode & "," & _
            ")" & _
    ")," & _
    "SUMIFS(" & _
            "DataDump[On Hand Qty (Units)]," & _
            "DataDump[Business Unit]," & BUcode & "," & _
            "DataDump[Location Short Description], binshelv" & _
    ")" & _
    ")"
    ActiveCell.FormulaR1C1 = strFormula
End Sub

使用双引号表达字面的报价标记转弯

=SUMIFS(C8:C11,A8:A11,"colACriteria",B8:B11,"ColBCriteria")

进入

"=SUMIFS(C8:C11,A8:A11,""colACriteria"",B8:B11,""ColBCriteria"")"