Agfdhyk

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:

[edit] 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_Ende + 1, Int_links).Address = Latest date

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

Your formula is over 255 characters, so you cannot enter it directly via VBA.

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

    '214 Characters

    .FormulaArray = "=(PRODUCT(IF(MAX(1)=SMALL(IF((MAX(1)>=" & sh_Ov.Cells(int_Start, 3).Address & "),MAX(1),999999),1),Daten!A1:A" & b & "-SUM(0,0),1),IF((MAX(1)>SMALL(IF((MAX(1)>=" & sh_Ov.Cells(int_Start, 3).Address & "),MAX(1)),1))*((MAX(1)<=" & 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"



     'Swap all instances of MAX(1) for your range in the Daten sheet

    .Replace("MAX(1)", "Daten!B1:B" & b, LookAt:=xlPart)

End With

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