VBA FormulaArray - 无法设置 Range 类的 FormulaArray 属性

Question

首先，我正在尝试编写一个宏程序，它可以让用户使用它来查找最接近用户输入的总目标的数字。

在第一个图中，数量列分别显示了它们自己标题的数量。目标是来自用户的输入。水平的 A、B、C、D 用于显示用户使用的标题。

文件执行后我收到此错误消息。



有什么建议吗？谢谢...

Sub Option1()
    Dim c As Long
    For c = 5 To 8
        Sheet1.Cells(5, c).FormulaArray = _
          "=INDEX(MOD(INT((ROW(R2C2:INDEX(C2,2^ROWS(R2C2:R9C2)))-1)/2^(TRANSPOSE(MATCH(ROW(R2C2:R9C2),ROW(R2C2:R9C2)))-1)),2)*TRANSPOSE(R2C2:R9C2),MATCH(MIN(ABS(MMULT(MOD(INT((ROW(R2C2:INDEX(C2,2^ROWS(R2C2:R9C2)))-1)/2^(TRANSPOSE(MATCH(ROW(R2C2:R9C2),ROW(R2C2:R9C2)))-1)),2),R2C2:R9C2)-R1C5)),ABS(MMULT(MOD(INT((ROW(R2C2:INDEX(C2,2^ROWS(R2C2:R9C2)))-1)/2^(TRANSPOSE(MATCH(ROW(R2C2:R9C2),ROW(R2C2:R9C2)))-1)),2),R2C2:R9C2)-R1C5),0),0)"
    Next c
End Sub

Answer 1

您应该直接应用于整个范围，而不是在每个单元格中:

Sub Option1()
    Sheet1.Range(Sheet1.Cells(5,5),Sheet1.Cells(5, 8)).Clear
    Sheet1.Range(Sheet1.Cells(5,5),Sheet1.Cells(5, 8)).FormulaArray = _
       "=INDEX(MOD(INT((ROW(R2C2:INDEX(C2,2^ROWS(R2C2:R9C2)))-1)/2^(TRANSPOSE(MATCH(ROW(R2C2:R9C2),ROW(R2C2:R9C2)))-1)),2)*TRANSPOSE(R2C2:R9C2),MATCH(MIN(ABS(MMULT(MOD(INT((ROW(R2C2:INDEX(C2,2^ROWS(R2C2:R9C2)))-1)/2^(TRANSPOSE(MATCH(ROW(R2C2:R9C2),ROW(R2C2:R9C2)))-1)),2),R2C2:R9C2)-R1C5)),ABS(MMULT(MOD(INT((ROW(R2C2:INDEX(C2,2^ROWS(R2C2:R9C2)))-1)/2^(TRANSPOSE(MATCH(ROW(R2C2:R9C2),ROW(R2C2:R9C2)))-1)),2),R2C2:R9C2)-R1C5),0),0)"

End Sub

或者

Sub Option2()
    Sheet1.Range("E5:I5").Clear
    Sheet1.Range("E5:I5").FormulaArray = _
       "=INDEX(MOD(INT((ROW(R2C2:INDEX(R2C3,2^ROWS(R2C2:R9C2)))-1)/2^(TRANSPOSE(MATCH(ROW(R2C2:R9C2),ROW(R2C2:R9C2)))-1)),2)*TRANSPOSE(R2C2:R9C2),MATCH(MIN(ABS(MMULT(MOD(INT((ROW(R2C2:INDEX(R2C3,2^ROWS(R2C2:R9C2)))-1)/2^(TRANSPOSE(MATCH(ROW(R2C2:R9C2),ROW(R2C2:R9C2)))-1)),2),R2C2:R9C2)-R1C5)),ABS(MMULT(MOD(INT((ROW(R2C2:INDEX(R2C3,2^ROWS(R2C2:R9C2)))-1)/2^(TRANSPOSE(MATCH(ROW(R2C2:R9C2),ROW(R2C2:R9C2)))-1)),2),R2C2:R9C2)-R1C5),0),0)"

End Sub