ArrayFormel per VBA zu lange => Error

[HeilyDioh]

Hallo zusammen,

ich möchte gerne per VBA eine recht lange Matrix-Formel in eine Zelle einfügen. Ich habe auch den Macro-Recorder benutzt. Leider gibt es eine Fehlermeldung:

Wenn ich ins Modul schaue, habe ich festgestellt, dass bei den Umbrüchen Teile der Formel einfach fehlen.

Die Originalformel lautet wie folgt:

PHP-Code:

+IF(E2="";"";INDEX(Table4[Base price EUR];MATCH(IFERROR("'"&NUMBERVALUE(A2);"'"&(A2))&"#"&IF(FlatBoM!$L$2*C2>=MAX((Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2)))*Table4[Min.Or.Qt]);MAX((Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2)))*Table4[Min.Or.Qt]);IF(FlatBoM!$L$2*C2<=MIN(IF(Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2));Table4[Min.Or.Qt];MAX((Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2)))*Table4[Min.Or.Qt])+1));MIN(IF(Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2));Table4[Min.Or.Qt];MAX((Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2)))*Table4[Min.Or.Qt])+1));MAX(IF((Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2)))*(Table4[Min.Or.Qt]>MIN(IF(Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2));Table4[Min.Or.Qt];MAX((Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2)))*Table4[Min.Or.Qt])+1)))*(Table4[Min.Or.Qt]<=FlatBoM!$L$2*C2);Table4[Min.Or.Qt];MIN(IF(Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2));Table4[Min.Or.Qt];MAX((Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2)))*Table4[Min.Or.Qt])+1))))));Table4[Material]&"#"&Table4[Min.Or.Qt];0))/INDEX(Table4[/];MATCH(IFERROR("'"&NUMBERVALUE(A2);"'"&(A2))&"#"&IF(FlatBoM!$L$2*C2>=MAX((Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2)))*Table4[Min.Or.Qt]);MAX((Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2)))*Table4[Min.Or.Qt]);IF(FlatBoM!$L$2*C2<=MIN(IF(Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2));Table4[Min.Or.Qt];MAX((Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2)))*Table4[Min.Or.Qt])+1));MIN(IF(Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2));Table4[Min.Or.Qt];MAX((Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2)))*Table4[Min.Or.Qt])+1));MAX(IF((Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2)))*(Table4[Min.Or.Qt]>MIN(IF(Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2));Table4[Min.Or.Qt];MAX((Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2)))*Table4[Min.Or.Qt])+1)))*(Table4[Min.Or.Qt]<=FlatBoM!$L$2*C2);Table4[Min.Or.Qt];MIN(IF(Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2));Table4[Min.Or.Qt];MAX((Table4[Material]=IFERROR("'"&NUMBERVALUE(A2);"'"&(A2)))*Table4[Min.Or.Qt])+1))))));Table4[Material]&"#"&Table4[Min.Or.Qt];0))) 


Ich hab mal von einer Lösung mit REPLACE gelesen. Kann mir bitte jemand helfen??? Vielen Dank!

Hier der Code vom Macro-Recorder:

Code:

Sub price()

   Sheets("Quick Priced Selection").Select
   Range("F2").Select
   Selection.FormulaArray = _
       "=+IF(RC[-1]="""","""",INDEX(Table4[Base price EUR],MATCH(IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5]))&""#""&IF(FlatBoM!R2C12*RC[-3]>=MAX((Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])))*Table4[Min.Or.Qt]),MAX((Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])))*Table4[Min.Or.Qt]),IF(FlatBoM!R2C12*RC[-3]<=MIN(IF(Table4[Materia" & _
       "OR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])),Table4[Min.Or.Qt],MAX((Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])))*Table4[Min.Or.Qt])+1)),MIN(IF(Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])),Table4[Min.Or.Qt],MAX((Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])))*Table4[Min.Or.Qt])+1)),MAX(IF((Table4[Materi" & _
       "OR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])))*(Table4[Min.Or.Qt]>MIN(IF(Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])),Table4[Min.Or.Qt],MAX((Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])))*Table4[Min.Or.Qt])+1)))*(Table4[Min.Or.Qt]<=FlatBoM!R2C12*RC[-3]),Table4[Min.Or.Qt],MIN(IF(Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-" & _
       "&(RC[-5])),Table4[Min.Or.Qt],MAX((Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])))*Table4[Min.Or.Qt])+1)))))),Table4[Material]&""#""&Table4[Min.Or.Qt],0))/INDEX(Table4[/],MATCH(IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5]))&""#""&IF(FlatBoM!R2C12*RC[-3]>=MAX((Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])))*Table4[Min.Or.Qt])" & _
       "le4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])))*Table4[Min.Or.Qt]),IF(FlatBoM!R2C12*RC[-3]<=MIN(IF(Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])),Table4[Min.Or.Qt],MAX((Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])))*Table4[Min.Or.Qt])+1)),MIN(IF(Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5]" & _
       "[Min.Or.Qt],MAX((Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])))*Table4[Min.Or.Qt])+1)),MAX(IF((Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])))*(Table4[Min.Or.Qt]>MIN(IF(Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])),Table4[Min.Or.Qt],MAX((Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])))*" & _
       "n.Or.Qt])+1)))*(Table4[Min.Or.Qt]<=FlatBoM!R2C12*RC[-3]),Table4[Min.Or.Qt],MIN(IF(Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])),Table4[Min.Or.Qt],MAX((Table4[Material]=IFERROR(""'""&NUMBERVALUE(RC[-5]),""'""&(RC[-5])))*Table4[Min.Or.Qt])+1)))))),Table4[Material]&""#""&Table4[Min.Or.Qt],0)))"
End Sub

[schauan]

Hallöchen,

Warum da Formelteile fehlen kann ich nicht sagen

Mal allgemein - wenn eine Formula auf Grund von Limitationen zu lang ist, wirst Du es nicht lösen können, ohne nach Vereinfachungen der Formel zu suchen.
Ansonsten, wenn Du eine lange Zeichenkette hast, versuche mal, sie aus einzelnen Teilen zusammenzusetzen. Die Eingabe der Formelteile zeichnest Du wieder auf.

strFormula="1+2+3"
strFormula=strFormula & "+4+5+6"
strFormula=strFormula & "+7+8+9"
…

Alternativ nimmst Du die Formel, die glücklicherweise schon englisch ist, in einen Texteditor und ersetzt z.B. (A2) durch (RC[-5]) usw.

Du schreibst

Leider gibt es eine Fehlermeldung:

Diese Fehlermeldung

Wenn ich ins Modul schaue, habe ich festgestellt, dass bei den Umbrüchen Teile der Formel einfach fehlen.

kenne ich aber noch nicht :22:

[Klaus-Dieter]

Hallo,

Formeln per VBA eintragen ist aus meiner Sicht ein bisschen grenzwertig. Speziell, wenn es sich um solche Monsterformeln handelt. Da kann man doch gleich die Berechnung(en) per Makro ausführen und das Ergebnis in die Liste schreiben.