variable in formula array vba

[Konrado]

Hey everybody

I have one big problem with variable in my formula array. One time I asked about it here, but now one thing doesn't work.

I want to put in vba such formula array with variable

=SUM(($E$4=DATA!$D$4:$D$2526)*(IF($E$5<>"TOTAL";$E$5=DATA!$E$4:$E$2526;1))*($C14=DATA!$N$4:$N$2526)*($D14=DATA!$L$4:$L$2526)*(E$11=DATA!$S$2:$BN$2)*(CHOOSE($F$218;$H$218=DATA!$S$3:$BN$3;$H$218>=DATA!$S$3:$BN$3;$H$218<DATA!$S$3:$BN$3<data!$s$3:$bn$3<data!$s$3:$bn$3< strong=""> </data!$s$3:$bn$3<data!$s$3:$bn$3<><data!$s$3:$bn$3< strong="">))*(DATA!$S$4:$BN$2526))</data!$s$3:$bn$3<><data!$s$3:$bn$3< strong="">

<data!$s$3:$bn$3 <="" strong="">Here my macro code

</data!$s$3:$bn$3><data!$s$3:$bn$3 <="" strong=""></data!$s$3:$bn$3></data!$s$3:$bn$3<><data!$s$3:$bn$3< strong="">Sub PoprawkiView()


Dim lastrow As Long


lastrow = Sheets("DATA").Range("A" & Rows.Count).End(xlUp).Row


With Sheets("View")


.Range("E14").FormulaArray = "=SUM(($E$4=data!$D$4:$D$" & lastrow & ")*(IF($E$5<>""TOTAL"",$E$5=data!$E$4:$E$" & lastrow & ",1))*($C14=data!$N$4:$N$" & lastrow & ")*($D14=data!$L$4:$L$" & lastrow & ")*(E$11=data!$S$2:$BN$2)*(CHOOSE($F$218,$H$218=data!$S$3:$BN$3,$H$218>=data!$S$3:$BN$3))*(data!$S$4:$BN$" & lastrow & "))"
.Range("E14").Copy .Range("F14:H14")
.Range("E14").Copy .Range("E15:H24,E26:H40,E42:H70,E72:H78,E80:H108,E110:H118,E120:H121,E123:H133,E135:H140")
.Range("E14").Copy .Range("E144:H145,E147:H148")


End With</data!$s$3:$bn$3<><data!$s$3:$bn$3< strong="">
<data!$s$3:$bn$3 <="" strong=""><data!$s$3:$bn$3 and="" excel="" shows="" errors.="" what="" is="" the="" reason="" how="" can="" i="" put="" formula="" correctly?=""


</data!$s$3:$bn$3></data!$s$3:$bn$3><data!$s$3:$bn$3 <="" strong=""><data!$s$3:$bn$3 and="" excel="" shows="" errors.="" what="" is="" the="" reason="" how="" can="" i="" put="" formula="" correctly?=""
</data!$s$3:$bn$3></data!$s$3:$bn$3></data!$s$3:$bn$3<><data!$s$3:$bn$3< strong=""><data!$s$3:$bn$3 <="" strong=""><data!$s$3:$bn$3 and="" excel="" shows="" errors.="" what="" is="" the="" reason="" how="" can="" i="" put="" formula="" correctly?=""
The problem is that I want to add bolded part of formula in vba and then it starts to show errors. I tried to add </data!$s$3:$bn$3>$H$218<data!$S$3:$BN$3<data!$s$3:$bn$3< strong="">, </data!$s$3:$bn$3<><data!$s$3:$bn$3< strong="">b</data!$s$3:$bn$3<><data!$s$3:$bn$3< strong=""></data!$s$3:$bn$3<><data!$s$3:$bn$3< strong="">ut it doesn't work.

<data!$s$3:$bn$3 and="" excel="" shows="" errors.="" what="" is="" the="" reason="" how="" can="" i="" put="" formula="" correctly?=""
Old topic here: </data!$s$3:$bn$3>http://www.mrexcel.com/forum/excel-questions/804767-formulaarray-variable.html</data!$s$3:$bn$3<><data!$s$3:$bn$3< strong=""></data!$s$3:$bn$3<></data!$s$3:$bn$3></data!$s$3:$bn$3<><data!$s$3:$bn$3< strong=""><data!$s$3:$bn$3 <="" strong=""></data!$s$3:$bn$3></data!$s$3:$bn$3<>

 

[Konrado]

Please remove this topic. Sorry for problems.

Konrado

 

[shg]

You are limited to 255 characters in array formulas entered via VBA. See a workaround at http://www.mrexcel.com/forum/excel-questions/565464-error-long-array-formula.html

 

[Konrado]

I just wanted in bolded part put $H$218 < DATA!$S$3:$BN$3<data!$s$3:$bn$3< strong="">, </data!$s$3:$bn$3<><data!$s$3:$bn$3< strong="">but it doesn't work and has less than 255 characters.</data!$s$3:$bn$3<><data!$s$3:$bn$3< strong=""></data!$s$3:$bn$3<>

 

[shg]

Assign the formula to a string variable, then print it to the Immediate window:

sFrm =  "=SUM(all that stuff)"
Debug.Print sFrm

If it's syntactically correct, you should be able to copy directly from there and paste it into a cell. If it's not, keep trying until it is.