FormulaArray funktioniert nicht

[fullevent]

Hallo zusammen,

ich möchte in Excel 16 eine Matrix-Formel über ein Marko in eine Zelle schreiben.

Dazu mal den folgenden Code-Auszug:

Code:

If Cells(row, 6) = "Deutschland" And Not Cells(row, 5) = "" Then
formel = "=INDEX('Vertrieblerliste(neu)'!$A$6:$A$54,MIN(WENN('Vertrieblerliste(neu)'!$C$6:$C$54>E" & row & ",ZEILE('Vertrieblerliste(neu)'!$C$6:$C$54)))-6)"
formel_konv = Application.ConvertFormula(Formula:=formel, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1)
Cells(row, 11).FormulaArray = formel_konv
MsgBox "Die Ermittlung eines Vertrieblers war ERFOLGREICH", 64, "ERFOLGREICH"
Exit Sub
End If

If Cells(row, 6) <> "Deutschland" And Cells(row, 6) <> "" Then
formel = "=INDEX('Vertriebler International'!B:B,VERGLEICH(F" & row & ",'Vertriebler International'!A:A,1)+1))"
formel_konv = Application.ConvertFormula(Formula:=formel, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1)
Cells(row, 11).FormulaArray = formel_konv
MsgBox "Die Ermittlung eines Vertrieblers war ERFOLGREICH", 64, "ERFOLGREICH"
End If

Bei dem oberen Teil passiert folgendes:
Die Formel wird wie gewünscht in entsprechende Zelle geschrieben (als Matrixformel mit den {}-Klammern) allerdings lautet der Zelleninhalt immer #Name.
Wenn ich aber in die Zelle klicke und wiederum mit strg+shift+Enter abschließe passiert genau das was passieren soll.
Warum kann ich das nur händisch machen? Bzw. wo habe ich da einen Fehler? 

Beim unteren Teil steht in der Zelle gar keine Formel sondern lediglich #Wert dabei ist der Code oben und unten identisch (bis auf die Formel natürlich) :16:

Ich bin langsam echt am verzweifeln  :22:

Grüße
fullevent  

[fullevent]

So ich hab das jetzt mal alles mit der R1C1 Schreibweise ausprobiert, aber leider ohne Erfolg..

Hier nochmal der Code-Auszug dazu:

Code:

If Cells(row, 6) = "Deutschland" And Not Cells(row, 5) = "" Then
formel = "=INDEX('Vertrieblerliste(neu)'!Z6S1:Z54S1,MIN(WENN('Vertrieblerliste(neu)'!Z6S3:Z54S3>ZS(-6),ZEILE('Vertrieblerliste(neu)'!Z6S3:Z54S3)))-6)"
Cells(row, 11).FormulaArray = formel
MsgBox "Die Ermittlung eines Vertrieblers war ERFOLGREICH", 64, "ERFOLGREICH"
Exit Sub
End If

If Cells(row, 6) <> "Deutschland" And Cells(row, 6) <> "" Then
formel = "=INDEX('Vertriebler International'!S(-15),VERGLEICH(Z(-1)S(-11),'Vertriebler International'!S(-16),1)+1)"
Cells(row, 11).FormulaArray = formel
MsgBox "Die Ermittlung eines Vertrieblers war ERFOLGREICH", 64, "ERFOLGREICH"
End If

wenn ich das Marko ausführe, dann schreibt mir der obere Code die richtige Formel in die Zelle, allerdings setzt er jedes ZxSx in '
Das sieht dann wie folgt aus: 

{=INDEX('Vertrieblerliste(neu)'!'Z6S1':'Z54S1';MIN(WENN('Vertrieblerliste(neu)'!'Z6S3':'Z54S3'>'ZS'(-6);ZEILE('Vertrieblerliste(neu)'!'Z6S3':'Z54S3')))-6)}

Wenn ich hier die ' wieder lösche und mit strg+shift+Enter abschließe, dann wird die Zelle so beschrieben wie gewünscht.

Der untere Code-Teil hingegen gibt immer den Laufzeitfehler 1004 aus, obwohl mit copy&paste gearbeitet wurde.


Kann mir hier bitte bitte jemand helfen? 
Ich bekomme es nicht wie gewünscht hin und bei google komme ich immer wieder auf die selben Seiten, die mir leider alle nicht geholfen haben..

Cheers

[BoskoBiati]

Hallo,

ungetestet:

Code:

Cells(Row, 11).FormulaR1C1 = "=INDEX('Vertriebler International'!RC[-15],VERGLEICH(R[-1]C[-11],'Vertriebler International'!RC[-16],1)+1)"

Das ist keine Matrixformel, also kein Formulaarray!

Code:

If Cells(row, 6) = "Deutschland" And Not Cells(row, 5) = "" Then
     Cells(row, 11).Formulaarray ="=INDEX('Vertrieblerliste(neu)'!$A$6:$A$54;MIN(WENN('Vertrieblerliste(neu)'!$C$6:$C$54>E" & row & ";ZEILE('Vertrieblerliste(neu)'!$C$6:$C$54)))-6)"
     MsgBox "Die Ermittlung eines Vertrieblers war ERFOLGREICH", 64, "ERFOLGREICH"
     Exit Sub
End If

[fullevent]

Guten Morgen :)

Das ist keine Matrixformel, also kein Formulaarray!

Wieso sollte das keine Matrixformel sein?  
Händisch würde ich sie mit strg+shift+Enter abschließen.. 


Grüße

[schauan]

Hallöchen,

nur weil Du die Formel mit den Tasten abschließt, ist es noch keine Arrayformel :21: Arrayformel ist nur die obere, weil Du dabei mit dem MIN und dem WENN entsprechende Berechnungen benötigst. Die untere ist nur ein simpler INDEX mit VERGLEICH.

[Castor]

Morrn, Leute;
wenn ein Formeltext der Zelleigenschaft .FormulaArray zugewiesen wird, erscheint er in der Zelle auch als Matrixformel, egal, ob das nötig ist oder nicht. Die Eingabeform macht die Formel dazu, nicht ihr Inhalt!
Üblicherweise tritt das beobachtete Verhalten immer dann auf, wenn ein lokaler Formeltext einer Zell-Formel-Eigenschaft zugewiesen wird, die einen Formeltext in US-Original-Notation verlangt. Xl rechnet das dann zwar automatisch um, aber eben erst nach Zell-/Formel-Aktivierung per Klick. Normale Formeln können sowohl lokal als auch in Originalform (in A1- bzw Z1S1-/R1C1-Form) einer separaten Zelleigenschaft zugewiesen werden, was zu ihrer sofortigen Funktion führt. Für .FormulaArray gibt's aber nur eine Variante und das ist die US-Original-Formelnotation! Dabei ist es tatsächlich besser, die R1C1-Form zu verwenden, weil zumindest relative Adressen sonst u.U. nicht richtig erkannt werden.
Außerdem ist es natürlich generell nicht sinnvoll, eine R1C1-Formel halb lokal und halb in US-Form anzugeben. Da ist der Misserfolg von vornherein klar! Das hatte der Fragesteller schon richtiger, wenn auch in die falsche Richtung!
Schönes WE, Castor

[fullevent]

Hallöchen,

nur weil Du die Formel mit den Tasten abschließt, ist es noch keine Arrayformel  :21: Arrayformel ist nur die obere, weil Du dabei mit dem MIN und dem WENN entsprechende Berechnungen benötigst. Die untere ist nur ein simpler INDEX mit VERGLEICH.

Da könnten wir jetzt drüber disskutieren was die Formel genau zu einer Arrayformel für Excel macht, aber das Ergebnis wäre mir persönlich unwichtig  :32:
Excel verlangt von mir immer den "händischen Abschluss" um die Berechnung zu starten und das ist mein Problem

@Castor:
Was müsste ich dann genau Einstellen um die sofortige Funktion zu erhalten? Also wenn die Formel via VBA in die Zelle geschrieben wird.
Die Formel in R1C1-Schreibweise reicht irgendwie nicht aus :22:

[schauan]

Hallöchen,

eine solche Diskussion ist nicht der Punkt sondern der händische Abschluss. Wenn Dein Auto zu breit ist, musst Du eventuell auch erst die Spiegel anklappen, mir einem schmaleren Auto nicht
Musst Du die Formel denn auch händisch abschließen, wenn Du sie als "normale" Formel eintragen lässt?

Excel verlangt von mir immer den "händischen Abschluss" um die Berechnung zu starten und das ist mein Problem

Ich vermute nach diesem Satz, ja. So ganz sicher bin ich mir dabei jetzt aber nicht.

[fullevent]

Hallo,

Wenn Dein Auto zu breit ist, musst Du eventuell auch erst die Spiegel anklappen, mir einem schmaleren Auto nicht

also ich habe das jetzt mal ausprobiert und ein schmaleres Auto geparkt.. es hat reibungslos rein gepasst, aber die Spiegel waren trotzdem ab  xD

Wenn ich die Formel über .Formula in die Zelle schreibe, wird bei der Berechnung ein Fehler (bei der WENN-Funktion) angezeigt..
"Formula.png"
wenn ich die Formel via .FormulaArray in die Zelle schreiben lasse, dann sind zwar die {}-Klammern mit drin, aber die Berechnung wird nicht durchgeführt (wieder Fehler bei der WENN-Funktion)
"Formulaarray.png"
und wenn das Fenster "Formel auswerten" schließe, in die Formel klicke und mit strg+shift+Enter abschließe.. ja dann kommt plötzlich das Richtige raus.. ich würde gerne wissen warum
"haendischer_abschluss.png"
naja, aber vllt muss ich mich ja damit abfinden


Ps: sorry, ich wusste nicht wie ich jetzt auf die schnelle die Screen-shots anders anhängen soll :(

[RPP63]

Hi!
Auch, wenn prinzipiell bereits alles geschrieben wurde:

1. Setze den Cursor in eine funktionierende Matrix-Formel
   
2. Lies die R1C1-Notation (NICHT Z1S1!) mittels ?ActiveCell.FormulaR1C1 im Direktfenster aus.
   
3. Diesen String übergibst Du (evtl. " doppeln) per .FormulaArray = String an den Range.
   

Dein "MischMasch" verblüfft (nicht nur) Excel.  :19:

Gruß Ralf