FormulaArray with variable

[Konrado]

Hi I have problem with formula array, I want to put variable inside, but excel shows fail. What can be the reason?
Below the code:

Sub PoprawkiView()


Dim lastrow As Long


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


With Sheets("View")


.Range("E14").FormulaArray = "=SUM((R4C5=DATA!R4C4:R" & lastrow & "C4)*(IF(R5C5<>""TOTAL"",R5C5=DATA!R4C5:R2526C5,1))*(RC3=DATA!R4C14:R2526C14)*(RC4=DATA!R4C12:R2526C12)*(R11C=DATA!R2C19:R2C66)*(CHOOSE(R218C6,R218C8=DATA!R3C19:R3C66,R218C8>=DATA!R3C19:R3C66,R218C8<DATA!R3C19:R3C66))*(DATA!R4C19:R2526C66))"
.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


End Sub

 

[Arithos]

Hi I have problem with formula array, I want to put variable inside, but excel shows fail. What can be the reason?
Below the code:

Sub PoprawkiView()


Dim lastrow As Long


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


With Sheets("View")


.Range("E14").FormulaArray = "=SUM((R4C5=DATA!R4C4:R" & lastrow & "C4)*(IF(R5C5<>""TOTAL"",R5C5=DATA!R4C5:R2526C5,1))*(RC3=DATA!R4C14:R2526C14)*(RC4=DATA!R4C12:R2526C12)*(R11C=DATA!R2C19:R2C66)*(CHOOSE(R218C6,R218C8=DATA!R3C19:R3C66,R218C8>=DATA!R3C19:R3C66,R218C8<data!r3c19:r3c66))*(data!r4c19:r2526c66))"
.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


End Sub

It does work without the "& lastrow &" inserted, by just putting the number of rows in manually?



</data!r3c19:r3c66))*(data!r4c19:r2526c66))"

 

[vds1]

Can you post array formula that should appear in cell E14 ? Lets say Lastrow is 20

 

[Konrado]

Yes, without variable, it works. Original formula in E14

=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$4:$BN$2526))<data!$s$3:$bn$3))*(data!$s$4:$bn$2526))< html=""></data!$s$3:$bn$3))*(data!$s$4:$bn$2526))<>

 

[Konrado]

I would appreciate if this problem would have been solved by somebody. I really need it.

 

[vds1]

Try,

[COLOR=#0000cd]Sub [/COLOR]PoprawkiView()


[COLOR=#0000cd]Dim[/COLOR] lastrow [COLOR=#0000cd]As Long[/COLOR]


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


[COLOR=#0000cd]With[/COLOR] 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;$H$218))*(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")
[COLOR=#0000cd]End With[/COLOR]
[COLOR=#0000cd]
[/COLOR]
[COLOR=#0000cd]End Sub[/COLOR]

 

[Konrado]

Hi, thanks for involvement

It still doesn't work, I tried this way. I have no idea, why this fails. Even without variable formula doesn't work ( I tried it in R1C1 format as well).

 

[vds1]

Your posted formula shows that you have region setting ; as decimal separator

Give this a try,

[COLOR=#0000cd]Sub[/COLOR] PoprawkiView()

[COLOR=#0000cd]Dim[/COLOR] lastrow [COLOR=#0000cd]As Long[/COLOR]
lastrow = Sheets("DATA").Range("A" & Rows.Count).End(xlUp).Row

[COLOR=#0000cd]With [/COLOR]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,$H$218))*(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")
[COLOR=#0000cd]End With[/COLOR]

[COLOR=#0000cd]End Sub[/COLOR]

 

[Konrado]

It works! Thank You a lot! I really appreciate it.

 

[vds1]

Glad it helped. Have a nice day