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
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