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<>
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<>
Last edited: