Unable to set the formulaarray property of the range class 1004-CodePudding

I am trying to get sum of values from 1 column which is closest to the value that is in G2 cell, so i found this formula which works in excel (ctrl Shift Enter) but when I am trying to work this with vba, it throws error 1004. Please find below the formula that I am using. "G2" is where actual value is and C3:C10 is the range where it sums up each cells and find the closest value.

wbapp.Sheets(1).Range("V2:AC2").FormulaArray = "=INDEX(MOD(INT((ROW($C$3:INDEX($C:$C, 2^ROWS($C$3:$C$10)))-1)/2^(TRANSPOSE(MATCH(ROW($C$3:$C$10), ROW($C$3:$C$10)))-1)), 2)*TRANSPOSE($C$3:$C$10), MATCH(MIN(ABS(MMULT(MOD(INT((ROW($C$3:INDEX($C:$C, 2^ROWS($C$3:$C$10)))-1)/2^(TRANSPOSE(MATCH(ROW($C$3:$C$10), ROW($C$3:$C$10)))-1)), 2), $C$3:$C$10)-$G$2)), ABS(MMULT(MOD(INT((ROW($C$3:INDEX($C:$C, 2^ROWS($C$3:$C$10)))-1)/2^(TRANSPOSE(MATCH(ROW($C$3:$C$10), ROW($C$3:$C$10)))-1)), 2), $C$3:$C$10)-$G$2), 0), 0)"

CodePudding user response：

Because of the character limit, you could put your formula (or parts of it) in a defined name

Sub ThroughNames()
Dim MyLongFormula As String
'Set wbapp = ThisWorkbook
 MyLongFormula = "=INDEX(MOD(INT((ROW($C$3:INDEX($C:$C, 2^ROWS($C$3:$C$10)))-1)/2^(TRANSPOSE(MATCH(ROW($C$3:$C$10), ROW($C$3:$C$10)))-1)), 2)*TRANSPOSE($C$3:$C$10), MATCH(MIN(ABS(MMULT(MOD(INT((ROW($C$3:INDEX($C:$C, 2^ROWS($C$3:$C$10)))-1)/2^(TRANSPOSE(MATCH(ROW($C$3:$C$10), ROW($C$3:$C$10)))-1)), 2), $C$3:$C$10)-$G$2)), ABS(MMULT(MOD(INT((ROW($C$3:INDEX($C:$C, 2^ROWS($C$3:$C$10)))-1)/2^(TRANSPOSE(MATCH(ROW($C$3:$C$10), ROW($C$3:$C$10)))-1)), 2), $C$3:$C$10)-$G$2), 0), 0)"

    wbapp.Names.Add Name:="test", RefersTo:=MyLongFormula
   wbapp.Sheets(1).Range("V2:AC2").FormulaArray = "=test"
End Sub