FormulaArray funktioniert nicht
#1
Shocked 
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?  Huh

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  
Antwortento top
#2
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
Antwortento top
#3
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
Gruß
Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antwortento top
#4
Guten Morgen Smile

(09.06.2016, 15:33)BoskoBiati schrieb: Das ist keine Matrixformel, also kein Formulaarray!



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


Grüße
Antwortento top
#5
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.
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
Antwortento top
#6
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
[-] Folgende(r) 1 Benutzer sagt Danke an Castor für diesen Beitrag:
  • fullevent
Antwortento top
#7
(10.06.2016, 14:24)schauan schrieb: 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 Undecided

@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
Antwortento top
#8
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?
Zitat: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.
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
Antwortento top
#9
Hallo,


Zitat: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 Dodgy
"haendischer_abschluss.png"
naja, aber vllt muss ich mich ja damit abfinden Undecided


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


Angehängte Dateien Thumbnail(s)
           
Antwortento top
#10
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
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)
[-] Folgende(r) 1 Benutzer sagt Danke an RPP63 für diesen Beitrag:
  • fullevent
Antwortento top


Gehe zu:


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