.FormulaArray in VBA does not work

[espenskeie]

Hi

I try to past an Array Formula into one single cell, but the code stops at that line and I really cannot find the error. This is my formula:

' Formula for calculating number of days in interest period...
.Range("I4").FormulaArray = "=IF($N$1=360,IF(C4<>0,C4-INDIRECT(""C"" & MAX(IF($C$2:C3<>0,ROW($C$2:C3)))),0),IF(C4<>0,(YEAR(C4)-YEAR(INDIRECT(""C"" & MAX(IF($C$2:C3<>0,ROW($C$2:C3))))))*360+(MONTH(C4)-MONTH(INDIRECT(""C"" & MAX(IF($C$2:C3<>0,ROW($C$2:C3))))))*30+(DAY(C4)-DAY(INDIRECT(""C"" & MAX(IF($C$2:C3<>0,ROW($C$2:C3)))))),0))"


In column C I have dates in C2, and the NeXT is in C8, the one between are empty, so my expected value for I4 would be 0. And then I would autofill this formula to get the number of days from previuos date to current date.

Any suggestion to what is wrong?

Br Espen

 

[steve the fish]

Try a google search on FormulaArray and 255 characters. Its a bit tricky.

 

[espenskeie]

Try a google search on FormulaArray and 255 characters. Its a bit tricky.

Thanks. I didnt count the characters because i was thinking it was way less than 255. I see I might be wrong ??

Then I have to go back and read on the subject.

 

[espenskeie]

Thanks to Steve I found a way to solve this. I splitted the formula into two separate parts, and then "joined" them in again.

Dim theFormulaPart1 As String
Dim theFormulaPart2 As String

theFormulaPart1 ="=IFERROR(INDEX(Curve!D:D,MATCH(1,(DATE(RIGHT(Census!$BY2,4),LEFT(Census!$BY2,2),MID(Census!$BY2,4,2))-DATE(RIGHT(Census!$BM2,4),LEFT(Census!$BM2,2),MID(Census!$BM2,4,2))=Curve!$A:$A)*(Census!$T2=Curve!$C:$C),0)),""X()"")"
theFormulaPart2 ="IFERROR(INDEX(Curve!D:D,MATCH(1,(DATE(Year(Census!$BY2),Month(Census!$BY2),Day(Census!$BY2))-DATE(Year(Census!$BM2),Month(Census!$BM2),Day(Census!$BM2))=Curve!$A:$A)*(Census!$T2=Curve!$C:$C),0)),"""")"


theFormulaPart1 = Replace(theFormulaPart1,"""X()""", theFormulaPart2)

ActiveSheet.Range("CD2")= theFormulaPart1
​