Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

[VBA] Matrixformel erzeugen | Laufzeitfehler '1004':
#1
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!  Blush
Antwortento top
#2
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
Antwortento top
#3
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. Wink

Beispiel Quersumme aus Zahl unbekannter Länge:
BC
3648215733

ZelleFormel
C3=SUMME(1*TEIL(B3;ZEILE(INDIREKT("1:"&LÄNGE(B3)));1))
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. 
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
Antwortento top
#4
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 Blush 
Ne, das kann es nicht sein, bin was sowas angeht noch voll der N00b Dodgy 

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ß
Antwortento top
#5
(10.01.2020, 09:16)Basisa schrieb: 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.

Zitat: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:

Zitat: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.
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. 
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste