VBA FormulaArray - Unable to set the FormulaArray property of the Range class

2
votes

At first, I am trying to write a macro program which can let user use to find numbers that closest to a total target that input from user.

  • in the first figure, the quantity column is presenting amounts for their own title respectively. Target is an input from user. the horizontal A,B,C,D is use to show user which Title used.

enter image description here

I get this error message after the file execution.

enter image description here

Any advice? Thanks...

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
1
vbaexcelexcel-formula
Perhaps you should clear the existing array before ? You can't change part of an existing array. - Patrick Honorez

1 Answers

0
votes

You should apply to the whole range directly, not in each cell :

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

Or 

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