[VBA] Matrixformel erzeugen | Laufzeitfehler '1004':

[Basisa]

Guten Tag meine lieben Excel-Freunde,

ich habe nun soviel probiert, aber es klappt einfach nicht.

Ich wollte mittels VBA eine Matrixformel erzeugen, dass klappte bislang wunderbar.
Diese wurde von mir aber nun erweitert, jetzt kommt aber immer folgender Fehler:
Laufzeitfehler 1004 ; Die FormulaArray-Eigenschaft des Range-Objektes kann nicht festgelegt werden.

Internet meint, es sind eben zu viele Zeichen, was auch mit über 255 Zeichen wohl stimmen mag. Jedoch soll man mit replace dieses umgehen können.

Aber wie beschrieben, bei mir ging es nicht xD

Darum frage ich hier, ob einer von euch das hinbekommt.

Hier die Formel:

Code:

{=WENN(Einstellungen!$I$16=FALSCH;MAX(Einstellungen!AS8:AS1000028+Einstellungen!AT8:AT1000028);TEXT(MAX(Einstellungen!AS8:AS1000028+Einstellungen!AT8:AT1000028);"#.##0")&" ( "&TEXT(100/((Einstellungen!AL36)*2)*MAX(Einstellungen!AS8:AS1000028+Einstellungen!AT8:AT1000028);"[>9,94]00,0;___00,0")&" %)")}

So der Versuch bislang ohne replace:

Code:

Worksheets("Auswertungen").Range("B28").FormulaArray = "=IF(Einstellungen!R16C9=FALSE,MAX(Einstellungen!R[-20]C[43]:R[1000000]C[43]+Einstellungen!R[-20]C[44]:R[1000000]C[44]),TEXT(MAX(Einstellungen!R[-20]C[43]:R[1000000]C[43]+Einstellungen!R[-20]C[44]:R[1000000]C[44]),""#.##0"")&"" ( ""&TEXT(100/((Einstellungen!R[8]C[36])*2)*MAX(Einstellungen!R[-20]C[43]:R[1000000]C[43]+Einstellungen!R[-20]C[44]:R[1000000]C[44]),""[>9,94" & "]00,0;___00,0"")&"" %)"")" & ""

Besten Dank schon einmal für Eure Mühe! 

[Elex]

Hi
 
Wie du schon heraus gefunden hast liegt es an der Länge des Formelstring. (Max bin ich mir nicht sicher ca 190 Zeichen)
Eine Möglichkeit den String zu verkürzen wäre für Bereiche Namen zu Vergeben und in die Formel einzusetzen.
 
"=IF(Einstellungen!R16C9=FALSE,MAX(Bere1+Bere2),TEXT(MAX(Bere1+Bere2),""#.##0"")&"" ( ""&TEXT(100/((Einstellungen!R[32]C[33])*2)*MAX(Bere1+Bere2),""[>9,94]00,0;___00,0"")&"" %)"")"
 
Gruß Elex

[RPP63]

Moin!
Hier bietet sich auch die eher ungeliebte .SendKeys-Methode an.
Heißt:
Lange Formel als .Formula eintragen,
danach F2 und CSE-Abschluss.

Achtung:
Das Makro darf nicht aus dem VBE gestartet werden, sondern aus Excel mittels Alt+F8 (oder Schaltfläche oder …)

Sub CSE_Lang()

With Range("B28")

  .Select

  .Formula = _

  "=IF(Einstellungen!R16C9=FALSE,MAX(Einstellungen!R[-20]C[43]:R[1000000]C[43]+Einstellungen!R[-20]C[44]:R[1000000]C[44]),TEXT(MAX(Einstellungen!R[-20]C[43]:R[1000000]C[43]+Einstellungen!R[-20]C[44]:R[1000000]C[44]),""#.##0"")&"" ( ""&TEXT(100/((Einstellungen!R[8]C[36])*2)*MAX(Einstellungen!R[-20]C[43]:R[1000000]C[43]+Einstellungen!R[-20]C[44]:R[1000000]C[44]),""[>9,94" & "]00,0;___00,0"")&"" %)"")" & ""

End With

Application.SendKeys "{F2}"

Application.SendKeys "^+~"

End Sub

Gruß Ralf

---

Wichtiger Nachtrag:
Wenn Basisa sein Exel 365 auf aktuellem Stand hat, braucht es kein CSE mehr!
Excel erkennt mittlerweile selbständig, dass es sich um eine Matrixformel handelt.
Wir werden uns über kurz oder lang von den {} verabschieden dürfen.

Beispiel Quersumme aus Zahl unbekannter Länge:

	B	C
3	6482157	33

Zelle	Formel
C3	=SUMME(1*TEIL(B3;ZEILE(INDIREKT("1:"&LÄNGE(B3)));1))

[Basisa]

Hallo ihr, ja wo fange ich da an?!

Meine Version soll auf dem neuesten Stand sein, zu mindest bietet er mir keine Updates an ^^

Woher soll er automatisch wissen dass es eine Matrixformel ist, hast du da mehr Hintergrundwissen?
Das wäre rein für mich, da meine Tabelle auch andere nutzen die noch bis zu Office 2003 haben xD

SendKeys wäre in meiner Tabelle auch schwer umzusetzen, dazu müsste ich dann Userformen schließen, wieder öffnen etc., wie unprofessionell sieht das denn aus? ^^

Lange Rede, kurzer Sinn.
Ich habe das mal mit dem Bereich versucht wie von @Elex beschrieben.

Für andere: 
-> Reiter "Formeln"
-> Namensmanager
-> Name vergeben, hier Bereich
-> bezieht sich auf, hier =Einstellungen!AS8:AS1000028+Einstellungen!AT8:AT1000028

Aufpassen, er könnte automatisch "" ran setzen, was vielleicht nicht gewollt ist!

Code:

=WENN(Einstellungen!$I$16=FALSCH;MAX(Bereich);TEXT(MAX(Bereich);"#.##0")&" ( "&TEXT(100/((Einstellungen!AL36)*2)*MAX(Bereich);"[>9,94]00,0;___00,0")&" %)")

Funktioniert, besten Dank!

Verstehe nur nicht, warum dann meine Kürzung mit den Replace nicht funktioniert hat, die war genauso kurz xD
Aber auch das habe ich dann noch einmal versucht und siehe da:

Code:

Worksheets("Auswertungen").Range("B28").FormulaArray = "=IF(Einstellungen!R16C9=FALSE,MAX(Bereich),TEXT(MAX(Bereich),""#.##0"")&"" ( ""&TEXT(100/((Einstellungen!R[8]C[36])*2)*MAX(Bereich),""[>9,94]00,0;___00,0"")&"" %)"")"
Worksheets("Auswertungen").Range("B28").Replace "Bereich", "Einstellungen!AS8:AS1000028+Einstellungen!AT8:AT1000028"

Auch das geht nun!

Schöne Ideen, vielleicht war es aber auch einfach schon spät  
Ne, das kann es nicht sein, bin was sowas angeht noch voll der N00b  

Aber mal noch so eine Frage, ab wann lohnt es sich With zu machen zwecks Schnelligkeit?
Schon ab wie oben zwei oder sollten es doch schon mehr sein?

Gruß

[RPP63]

Aber mal noch so eine Frage, ab wann lohnt es sich With zu machen zwecks Schnelligkeit?

Vielleicht solltest Du Dich zunächst fragen, warum Du eine Matrix-Formel auf 1.000.021 Zeilen ansetzt!
"With" hat weniger mit Geschwindigkeit, sondern eher mit Ästhetik zu tun.

Woher soll er automatisch wissen dass es eine Matrixformel ist, hast du da mehr Hintergrundwissen?

"Er" weiß das ungefähr so, als wenn "er" weiß, dass SUMME() addieren soll.
Man könnte natürlich die Programmierer fragen (wohl wissend, dass die nix sagen), man könnte es schlicht auch einmal ausprobieren!

Obige Quersumme in Excel-Online "schnallt" das ohne den (hier auch gar nicht möglichen) CSE-Abschluss:
   

---

Ach ja, nun kommt sie doch noch, meine verquere Meinung:

SendKeys wäre in meiner Tabelle auch schwer umzusetzen, dazu müsste ich dann Userformen schließen, wieder öffnen etc., wie unprofessionell sieht das denn aus?

Mir sind extrem wenige UserForms begegnet, die auch nur annähernd den Anspruch auf Professionalität bieten konnten.
Wohlgemerkt betrifft dies Excel, Word und Konsorten.
Ganz im Gegenteil wurde dort nur der Anspruch auf Professionalität erhofft, der Tapeten an Code bedingte, um nur die Hälfte der Fehler auszumerzen, die in der GUI (sorry, Jeanie, schon wieder das böse Wort) kein Problem darstellen.