代码之家 › 专栏 › 技术社区 › Ans

excel vba公式字符串:数组公式-如何模拟ctrl+shift+enter-press

excel-vba vba excel

3

Ans · 技术社区 · 6 年前

我有一个包含数据的工作表和一些列,我通过vba用公式填充它们。我要处理的公式是一个数组公式,如下所示:

Workbooks(job_file).Worksheets(1).Cells(h + b, 195).Formula = _
            "{=IF(MAX(IF(B2:M2>$FY" & currentRow & ",$B$1:$M$1))=0,0," & _
              "MAX(IF(sheet1!B2:M2>$FY" & currentRow & "," & _
                                "sheet1!$B$1:$M$1)))+1}"

它应该是一个数组公式,所以我把 {} 在那里。但是,当运行时,它只是在单元格中显示公式的文本,而不进行计算。我必须手动拆下支架,然后按 Ctrl+Shift+Enter 我自己。

有什么办法可以避免呢?我有很多行,不能每行都输入。

我试着不加括号地运行它,它工作了,但是 #VALUE! 错误,也可以通过应用 ctrl+shift+enter键 是的。

2 回复 | 直到 6 年前

1

4

user4039065user4039065 6 年前

要使用模拟的ctrl+shift+enter(也称为cse)创建数组公式,请使用 Range.FormulaArray Property 而不是 Range.Formula Property 并让excel添加“大括号”。

with Workbooks(job_file).Worksheets(1)
    .Cells(h + b, 195).FormulaArray = _
        "=IF(MAX(IF(B2:M2>$FY" & currentRow & ",$B$1:$M$1))=0,0," & _
            "MAX(IF(sheet1!B2:M2>$FY" & currentRow & ", sheet1!$B$1:$M$1)))+1"
end with

我注意到你的配方中 B2:M2 和 sheet1!B2:M2 是的。他们不应该 二者都 是 第1张!B2:平方米 是吗?

有一些考虑。

运行时错误:1004-太长。公式数组的字符限制是255,但是 there are work-arounds 是的。
运行时错误:1004-字符串断开。请记住,一个带引号的字符串中的所有引号都必须加一倍。当试图通过vba将公式写入单元格时,这很容易成为最常见的错误原因之一。提示: TEXT(,) 可以代替 "" 所以你不用打字 """" 对于零长度的字符串。
FormulaArray同时接受XLR1C1和XLA1样式的公式。如果可以将头绕在xlr1c1样式的公式语法上,则通常更容易在xlr1c1中构造连接的公式字符串,因为可以使用数字表示列编号,而不是尝试将列序号转换为列字母。但是,不要尝试在同一公式中混合和匹配XLA1和XLR1C1范围引用;它们必须都是一种或另一种样式。
如果您在制定将被接受为公式的字符串时遇到困难,请打勾(例如 ' )在第一个等号前面,然后运行代码并返回到工作表以查看放入了什么。在工作表上进行修改,直到有工作公式,然后将这些修改传输到VBA代码。

2

tigeravatar 6 年前

需要注意的是,这看起来可以在不使用数组公式的情况下完成,如下所示:

Workbooks(job_file).Worksheets(1).Cells(h + b, 195).Formula = _
        "=IF($FY" & currentRow & ">MAX(B2:M2),0," & _
         "MAX(INDEX((B2:M2>$FY" & currentRow & ")*$B$1:$M$1,)))+1"