FormulaArray (Matrixformule) is te lang in VBA

[Tomacro]

Beste Allemaal,

Ik probeer een FormulaArray (Matrixformule {}) te maken in VBA. Op internet heb ik gelezen dat deze niet langer mag zijn dan 255 tekens.
Om dit op te lossen is het mogelijk om de formule in stukken te knippen en deze te vervangen. Nu ben ik hier druk mee bezig, maar op een of andere manier lukt het mij niet.
Wanneer ik onderstaande code invoer geeft hij voor "AAA" de uitkomst van 'Part2' weer. Helaas doet hij dit als tekst en niet als (werkende) formule. Voor "BBB" geeft hij gewoon "BBB" weer.
Weet iemand waarom dit zo is? Wat doe ik fout? Ik hoop dat iemand kan helpen!

Dim Part1 As String
Dim Part2 As String
Dim Part3 As String
Dim Part4 As String
    
    
Part1 = "=IFERROR(IF(OR(IF(RC[-4]=""DIRECT"",""AAA"",""BBB"")=R1C5+16,IF(RC[-4]=""DIRECT"",""AAA"",""BBB"")=R1C5-5),""Bestellen"",IF(RC[-4]=""DIRECT"",""AAA"",""BBB"")),""Bestellen"")"
Part2 = "MIN(IF(RC[-20]:RC[-5]>=0,COLUMN(RC[-20]:RC[-5])))-5+R1C5"
Part3 = "MIN(IF(""CCC"",COLUMN(INDIRECT((CELL(""adres"",(INDEX(OFFSET(RC[-20],0,0,COUNTA(C5),16),1,MIN(IF(RC[-20]:RC[-5]<=-0.1,COLUMN(RC[-20]:RC[-5])))-4))))):RC[-5])))-5+R1C5"
Part4 = "INDIRECT((CELL(""adres"",(INDEX(OFFSET(RC[-20],0,0,COUNTA(C5),16),1,MIN(IF(RC[-20]:RC[-5]<=-0.1,COLUMN(RC[-20]:RC[-5])))-4))))):RC[-5]>=0"
    
    
With Sheets("Blad2").Range("Y2")
    .FormulaArray = Part1
    .Replace What:="AAA", Replacement:=Part2, Lookat:=xlPart, MatchCase:=True
    .Replace What:="BBB", Replacement:=Part3, Lookat:=xlPart, MatchCase:=True
    .Replace What:="CCC", Replacement:=Part4, Lookat:=xlPart, MatchCase:=True
  
End With

 

[HSV]

Plaats het bestand eens met de formule in een cel zodat ik deze eens kan testen (als ik er zo naar kijk lijkt die mij niet juist opgebouwd).
Doe dat in een .xlsb extensie zodat de code er ook in zit.

 

[Tomacro]

Hi HSV,

Bedankt dat je er naar wil kijken! Bijgaand het bestand.

Ik zal even uitleggen wat de bedoeling is van de Excel.

Je ziet het verbruik van de voorraad over de komende 16 weken.
Vervolgens zie je in de kolommen achter de weeknummers in welke week ze als eerst te kort komen, hoeveel er tekort komen en over hoeveel weken dat dan is.
Deze matrixformule moet aangeven wanneer de voorraad (voor het eerst) weer wordt aangevuld.

Wellicht dat de formule zelf nog vereenvoudigd kan worden, dat zou flink in de lengte van de formule kunnen schelen.

Meteen maar even een korte "bijvraag" over vereenvoudigden.
Kan onderstaande code makkelijker?

=ALS(OF(ALS(X;Y;Z)=5;ALS(X;Y;Z)=8);WAAR;ONWAAR)

Bekijk bijlage FormulaArray.xlsb

 

[snb]

In U2 de matrixformule:

=INDEX($E1:$T1;1;MIN(IF($E2:$T2<0;COLUMN($E3:$T3)))-4)

in V2:

=INDEX($E2:$T2;1;MIN(IF($E2:$T2<0;COLUMN($E3:$T3)))-4)

In je bestand ontbreken bestelcriteria.
Het bevat nog wel een aantal redeneerfouten.

 

[Tomacro]

Beste snb,

bedankt voor je reactie!

De data wordt geëxporteerd uit een ERP systeem en heeft daardoor een dynamisch bereik. Dat is de reden waarom er een VERSCHUIVING in de INDEX formule zit.
Jouw matrixformule maakt het wel eenvoudiger. Echter werken U2, V2 en W2 zo perfect. Het gaat vooral om Y2.

Bestelcriteria is niet van belang. De lijst is een soort escalatielijst. Hij signaleert waar gaat het 'echt' mis gaat. En dat is alleen als de voorraad onder 0 komt.

Groet,

Tom

 

[snb]

Als je het beter weet weet je het beter.

 

[Tomacro]

Hi HSV,

Ben je hier nog een beetje uitgekomen? Het zal toch niet dat ik een probleem heb dat niet op te lossen valt
Ik hoor het graag van je!

Groet!

 

[HSV]

Volg de syntaxis van de functies.
Ik zou niet weten wat onderstaande in een formule thuis hoort.

"MIN(IF(RC[-20]:RC[-5]>=0,COLUMN(RC[-20]:RC[-5])))-5+R1C5";"BBB")=$E$1+16

Edit: een verklaring voor de dubbele quotes zou kunnen zijn dat ik de Nederlandse versie heb en 'rc', 'rk' zou moeten zijn.