# VBA - Array Formula : Run Time Error 1004 on formulaArray even though the formula looks right. Example inside

EDIT: The solution from Chronocidal was very helpful. I implemented it and it works like a charm. Thanks to all your help. The remarks from Ron Reisenfeld and EvR helped a lot too and sent me in the right direction

can somebody help me with an array formula I am trying to insert into some cells with the formula.local function? There is probably something not right in the formula, but I just can’t find the reason… It would be a great help if someone can test it, since I only have a german version of excel and it may be possible that I have overseen any language specific reasons. I attached a file to test the formula (xlsx)

If I debug.print the formula looks like this:

``````=(PRODUCT(IF(Daten!B1:B12353=SMALL(IF((Daten!B1:B12353>=\$C\$25),Daten!B1:B12353,999999),1),Daten!A1:A12353-SUM(0,0),1),IF((Daten!B1:B12353>SMALL(IF((Daten!B1:B12353>=\$C\$25),Daten!B1:B12353),1))*((Daten!B1:B12353<=\$D\$33)),Daten!A1:A12353,1))^(1/(DAYS360(\$C\$25,\$D\$33)/360))-1)*100
``````

I used the formula in this example workbook: https://drive.google.com/open?id=1xCth5vo6G82lNDsclbj3FpUfYzvXsuEJ

In VBA the formula consists of plenty of variables those are:

 b = last row in the range i want to look at in the formula [/edit]

Daten!B1:B" & b = Row of Dates

sh_Ov.Cells(int_Start, 3).Address = earliest date (but not necessarily in the date row – that’s why I am working with the “small” formula)

Daten!A1:A" & b = Numbers I want to calculate the product of

``````ThisWorkbook.Sheets(1).Cells(x,y).FormulaArray = "=(PRODUCT(IF(Daten!B1:B" & b & "=SMALL(IF((Daten!B1:B" & b & ">=" & sh_Ov.Cells(int_Start, 3).Address & "),Daten!B1:B" & b & ",999999),1),Daten!A1:A" & b & "-SUM(0,0),1),IF((Daten!B1:B" & b & ">SMALL(IF((Daten!B1:B" & b & ">=" & sh_Ov.Cells(int_Start, 3).Address & "),Daten!B1:B" & b & "),1))*((Daten!B1:B" & b & "<=" & sh_Ov.Cells(int_Ende + 1, Int_links).Address & ")),Daten!A1:A" & b & ",1))^(1/(DAYS360(" & sh_Ov.Cells(int_Start, 3).Address & "," & sh_Ov.Cells(int_Ende + 1, Int_links).Address & ")/360))-1)*100"
``````

It would be great if anyone can have a look at this. So far I am not able to find the reason

Best regards Jens

Shorten your array-formula to 255 characters (with the help of defined names) and try again. (you exceed the maximum lenght)

https://docs.microsoft.com/en-us/office/vba/api/excel.range.formulaarray

Either change it to be shorter (such as using Named Ranges like EvR suggests), or use (technically valid) dummy code and swap it out afterwards with `Range.Replace`, like so:
``````With ThisWorkbook.Worksheets(1).Cells(x,y) 'Worksheets instead of Sheets will exclude Chart Sheets