VBA assign append formula to a range

Asked May 17 '22 at 13:52

Active May 17 '22 at 13:52

Viewed 19 times

matrixRange = "E2:H25"
matrixFormula = "=Append(Append(SUBSTITUTE(Input!B2,"",""),MatrixToList(Input!B3:B6,Input!C2:H2,Input!C3:H6),,,,1,1))"
addSheet.Range(matrixRange).Value = matrixFormula

After the execution, the formula in the range actually become:

"=@Append(Append(SUBSTITUTE(Input!B2,"",""),MatrixToList(Input!B3:B6,Input!C2:H2,Input!C3:H6),,,,1,1))"

it is the @ in the string that prevents excel from explaining.
Any idea?

asked May 17 '22 at 13:52

user3541195

1

Change `.Value` to `.Formula2`. – BigBen May 17 '22 at 13:53
changed to Formula2. Then the @ disappeared. That is fine. But the matrix all got a error flag like below: `#SPILL! #SPILL! #SPILL! #SPILL! – user3541195 May 17 '22 at 15:25
#SPILL! errors are well documented online - there's some other data in the way that's preventing the formula results from being spilled. – BigBen May 17 '22 at 15:31
1

ok. it succeed if I use Sheet.cells(1, 1).Fomula2 = matrixFormula – user3541195 May 17 '22 at 15:49

VBA assign append formula to a range

0 Answers0