FormulaArray mit Matrix

  • Excel

Es gibt 18 Antworten in diesem Thema. Der letzte Beitrag () ist von petaod.

    FormulaArray mit Matrix

    Hallo!

    Ich würde gerne wissen wie man folgende Formel in VBA umsetzt:

    {=VERGLEICH(A3&B3&C3&I3&J3;'6 - Gesamtbestellungen'!A:A&'6 - Gesamtbestellungen'!B:B&'6 - Gesamtbestellungen'!I:I&'6 - Gesamtbestellungen'!R:R&'6 - Gesamtbestellungen'!S:S;0)}

    Ich habe bereits ein Thema eröffnet, bei dem ich etwas ähnliches frage... Dabei geht es darum, dass der VBA-nicht mit der FormulaArray Formel geht....

    Deshalb frage ich nochmals explizit danach, denn ich habe mittlererweile ganz stark die Vermutung, dass es Excel vl. irgendwie Probleme hat, wenn man eine Matrizenformel eingibt... etc.

    Deshalb würde ich gerne wissen, wie man denn eine Matrixformel, so, wie ich sie hier habe, in VBA Code umsetzt....


    Wäre super, wenn mir da jemand weiterhelfen könnte, weil dann kann ich auch gleich das andere Thema und vl. sogar ein entferntes Folgethema bearbeiten und schließen... ;)

    Vielen Dank!

    VG Tim

    Starte den Makrorekorder, setz die Formel in einem Feld. Stoppe den Makro Rekorder. Schau dir an was dort als Formula in VBA gesetzt wird (ignorier die Cells.Select Teile)
    Das ist meine Signatur und sie wird wunderbar sein!

    ja das ist ja das Problem...

    das habe ich bereits gemacht!!

    Und das was mir unbegreiflich ist an der Sache ist...

    dass wenn ich dann das Makro, absolut unverändert, erneut ausführe bekomme ich eine Fehlermeldung!
    Heißt... Excel schreibt da selber irgendwas falsch oder keine Ahnung.... bin da echt ratlos... :((

    Nachtrag...:

    Wenn ichs aufzeichne... macht er mir folgenden code:

    Visual Basic-Quellcode

    1. Sub zeilennummerErmitteln5()
    2. '
    3. ' zeilennummerErmitteln5 Makro
    4. '
    6. '
    7. Range("W3").Select
    8. Selection.FormulaArray = _
    9. "=MATCH(RC[-22]&RC[-21]&RC[-20]&RC[-19]&RC[-18]&RC[-17]&RC[-16]&RC[-15]&RC[-14]&RC[-13],'6 - Gesamtbestellungen'!C[-22]&'6 - Gesamtbestellungen'!C[-21]&'6 - Gesamtbestellungen'!C[-14]&'6 - Gesamtbestellungen'!C[-15]&'6 - Gesamtbestellungen'!C[-11]&'6 - Gesamtbestellungen'!C[-9]&'6 - Gesamtbestellungen'!C[-7]&'6 - Gesamtbestellungen'!C[-6]&'6 - Gesamtbestellungen'!C[-" & _
    10. "Gesamtbestellungen'!C[-4],0)"
    11. End Sub


    wenn ich das dann aber ausführen möchte.... schmeist er mir einen laufzeitfehler... :((

    kann es irgendwie daran liegen... dass Excel STRG+UMSCHALT+ENTER nicht versteht oder so?? bzw. der Rekorder?? weil eben... das sollte eine Matrixformel werden... und { } schreibt der ja nit...

    aber sogar... wenn ichs händisch in den CODE mit {} ergänze... hauts immer noch nicht hin :(((

    ereza schrieb:

    ja das ist ja das Problem...

    das habe ich bereits gemacht!!

    Und das was mir unbegreiflich ist an der Sache ist...

    dass wenn ich dann das Makro, absolut unverändert, erneut ausführe bekomme ich eine Fehlermeldung!
    Heißt... Excel schreibt da selber irgendwas falsch oder keine Ahnung.... bin da echt ratlos... :((


    Und wie lautet die Fehlermeldung?

    Laufzeitfehler '1004': Die FormulaArray-Eigenschaft des Range-Objektes kann nicht festgelegt werden.

    hab dazu eh auch ein Thema aufgemacht... deshalb... wenn ichs hier gelöst bekomme... kann ich das andere dann auch als erledigt kennzeichnen...

    Laufzeitfehler '1004': Die FormulaArray-Eigenschaft des Range-Objektes kann nicht festgelegt werden.

    alles klar... anbei mein Excel file als zip gepackt... weil man da ja kein .xlsm hochladen kann...

    und... was meinst du mit Direktfenster - bzw. was ist ein Direktfenster?
    Also... wenn ich die Formel normal ins Feld eingebe bevor ich das Makro aufzeichnen möchte... funktionierts... und er schreibt mir die Korrekte Zeilennummer hin... - falls du jz das meinst....
    Dateien

    hmmm.... ich habe es einmal mit einem Excel probiert, ohne lange Blattbezeichnungen und mit weniger Parameter und da hats dann funktioniert... also auch die matrix-Schreibweise mit {}

    also... schließe ich diesen fehler mal aus....

    aber mir ist es leider nach wie vor unerklärlich warum das nicht hinhauen will... ich verstehs wirklich nicht... ich habe jz auch schon nochmals intensiver nachgeforscht und konnte auch rein gar nix finden...

    Wäre echt super, wenn mir da jemand weiterhelfen könnte... weil ich bin echt absolut ratlos... - weil das nämlich eigentlich gehen müsste!! dürfte also gar nicht sein... :cursing:

    Das Direktfenster ist im Screenshot markiert.
    Da kann man die Formel mittels Debug.print auslesen. Diese funktioniert dann auch im Makro.

    Visual Basic-Quellcode

    1. Private Sub CommandButton1_Click()
    3. Range("W3").FormulaArray = "=MATCH(A3&B3&C3&I3&J3,'6 - Gesamtbestellungen'!A:A&'6 - Gesamtbestellungen'!B:B&'6 - Gesamtbestellungen'!I:I&'6 - Gesamtbestellungen'!R:R&'6 - Gesamtbestellungen'!S:S,0)"
    4. Selection.AutoFill Destination:=Range("W3:W9"), Type:=xlFillDefault
    5. End Sub
    Bilder

    • Zwischenablage01.png

      104,09 kB, 1.578×999, 399 mal angesehen
    Gruß
    Peterfido

    Keine Unterstützung per PN!

    hallo peterfido!

    vielen herzlichen DANK!! habe ja gar nicht gewusst, dass man bei formula auch die ganz normale Schreibweise verwenden kann.... ich dachte immer, im CODE darf man nur die R-Schreibweise verwenden.... SUPER wieder was gelernt!!

    Jedoch hab ich noch eine Frage.... Wenn ich mehrere Paramater eingebe.... dann schmeißt er mir wieder einen Fehler.... Daher meine Frage:

    Kann man bei einer Matrixformel mit MATCH nur eine begrenzte Anzahl an Parametern eingeben?? Also maximal 5 Parameter?? - Weil das würde erklären warum es anfangs und auch jetzt nicht funktioniert sobald ich einen 6.ten Parameter hinzufüge...

    Weil das hier:

    Range("W3").FormulaArray = "=MATCH(A3&B3&C3&E3&I3&J3,'6 - Gesamtbestellungen'!A:A&'6 - Gesamtbestellungen'!B:B&'6 - Gesamtbestellungen'!I:I&'6 - Gesamtbestellungen'!L:L&'6 - Gesamtbestellungen'!R:R&'6 - Gesamtbestellungen'!S:S,0)"


    funktioniert schon nicht... und meine ursprünglich gedachte Formel:

    Range("W3").FormulaArray = "=MATCH(A3&B3&C3&D3&E3&F3&G3&H3&I3&J3,'6 - Gesamtbestellungen'!A:A&'6 - Gesamtbestellungen'!B:B&'6 - Gesamtbestellungen'!I:I&'6 - Gesamtbestellungen'!H:H&'6 - Gesamtbestellungen'!L:L&'6 - Gesamtbestellungen'!N:N&'6 - Gesamtbestellungen'!P:P&'6 - Gesamtbestellungen'!Q:Q&'6 - Gesamtbestellungen'!R:R&'6 - Gesamtbestellungen'!S:S,0)"


    natürlich schon gar nicht....

    Gibt's da also eine Begrenzung?? und falls JA, gibt es eine Methode, wie man diese Begrenzung "umgehen" bzw. erweitern kann?? Weißt du da was darüber? bzw. weiß einer von euch, hier im Forum, darüber etwas?

    Vielen DANK nochmals!!

    LG Tim

    Deine Formel könnte zu lang sein (329 Zeichen). Probiere mal das Blatt '6 - Gesamtbestellungen' in z.B. '6' umzubenennen und die Formel daran anzupassen.

    Visual Basic-Quellcode

    1. Const formell = "=MATCH(A3&B3&C3&D3&E3&F3&G3&H3&I3&J3,'6 - Gesamtbestellungen'!A:A&'6 - Gesamtbestellungen'!B:B&'6 - Gesamtbestellungen'!I:I&'6 - Gesamtbestellungen'!H:H&'6 - Gesamtbestellungen'!L:L&'6 - Gesamtbestellungen'!N:N&'6 - Gesamtbestellungen'!P:P&'6 - Gesamtbestellungen'!Q:Q&'6 - Gesamtbestellungen'!R:R&'6 - Gesamtbestellungen'!S:S,0)"
    2. Const formel = "=MATCH(A3&B3&C3&D3&E3&F3&G3&H3&I3&J3,'6'!A:A&'6'!B:B&'6'!I:I&'6'!H:H&'6'!L:L&'6'!N:N&'6'!P:P&'6'!Q:Q&'6'!R:R&'6'!S:S,0)"
    3. Const formelk = "=MATCH(A3&B3&C3&I3&J3,'6 - Gesamtbestellungen'!A:A&'6 - Gesamtbestellungen'!B:B&'6 - Gesamtbestellungen'!I:I&'6 - Gesamtbestellungen'!R:R&'6 - Gesamtbestellungen'!S:S,0)"
    6. Private Sub CommandButton1_Click()
    7. Debug.Print Len(formel)
    8. Range("W3").FormulaArray = formel
    10. Range("w3").AutoFill Destination:=Range("W3:W9"), Type:=xlFillDefault
    11. End Sub

    Gruß
    Peterfido

    Keine Unterstützung per PN!

    hmm... ich habe jz wirklich brutal runter gekürzt....:

    Visual Basic-Quellcode

    1. Range("W3").FormulaArray = "=MATCH(A3&B3&C3&D3&E3&F3&I3,'6 - Ges.Bestell.'!A:A&'6 - Ges.Bestell.'!B:B&'6 - Ges.Bestell.'!I:I&'6 - Ges.Bestell.'!H:H&'6 - Ges.Bestell.'!L:L&'6 - Ges.Bestell.'!N:N&'6 - Ges.Bestell.'!R:R,0)"


    Also das sind Gesamt, mit Leerzeichen, 220 Zeichen.... Aber das funktioniert immer noch nicht... :((

    Zum Vergleich - Die Formel:

    Visual Basic-Quellcode

    1. Range("W3").FormulaArray = "=MATCH(A3&B3&C3&D3&I3,'6 - Ges.Bestell.'!A:A&'6 - Ges.Bestell.'!B:B&'6 - Ges.Bestell.'!I:I&'6 - Ges.Bestell.'!H:H&'6 - Ges.Bestell.'!R:R,0)"


    funktioniert!

    Mir ist das wirklich ein Rätsel...

    Würde eigentlich echt gerne wissen woran das liegt.... Naja... vl. fällt jemanden dazu noch was ein...

    Vielen Dank!

    LG Tim

    @peterfido

    OK... Danke für den Tipp...

    ich werds dann echt mal so versuchen...

    Wenns aber dann tatsächlich an der Länge liegt... dann ist das bei weitem weniger wie 329 Zeichen... und auch weniger wie 220 Zeichen....


    @all
    weiß jemand von euch, wo man da in der Literatur nachlesen kann, wieviele Zeichen es maximal sein dürfen??



    mir kommts halt irgendwie komisch vor dass das so rummspinnt... weil ich habe Code Formeln die bei weitem länger sind... - ist jz halt kein "VERGLEICH" in Matrizenform... aber trotzdem... irgendwie seltsam...
    .. aber vl. gibt's ja da tatsächlich eine so große Beschränkung...??

    VG Tim

    Hallo,

    es liegt an der Länge des Sheet - Namens oder an einem Zeichen im Namen. Folgender Code verdeutlicht das. Wenn kurz auf 1 steht, dann wird überall der lange Name gegen einen kurzen getauscht und Excel 'frisst' die Formel. Interessant ist, dass man danach das Sheet wieder zurück umbenennen kann. Evtl. gibt es diese Einschränkungen bei der Formellänge nur per VBA.?

    Visual Basic-Quellcode

    1. Const kurz = 1
    2. Const formellang = "=MATCH(A3&B3&C3&D3&E3&F3&G3&H3&I3&J3,'6 - Gesamtbestellungen'!A:A&'6 - Gesamtbestellungen'!B:B&'6 - Gesamtbestellungen'!I:I&'6 - Gesamtbestellungen'!H:H&'6 - Gesamtbestellungen'!L:L&'6 - Gesamtbestellungen'!N:N&'6 - Gesamtbestellungen'!P:P&'6 - Gesamtbestellungen'!Q:Q&'6 - Gesamtbestellungen'!R:R&'6 - Gesamtbestellungen'!S:S,0)"
    3. Const formelkurz = "=MATCH(A3&B3&C3&D3&E3&F3&G3&H3&I3&J3,'6'!A:A&'6'!B:B&'6'!I:I&'6'!H:H&'6'!L:L&'6'!N:N&'6'!P:P&'6'!Q:Q&'6'!R:R&'6'!S:S,0)"
    5. Private Sub CommandButton1_Click()
    6. Dim sFormel As String
    8. If kurz = 1 Then
    9. sFormel = Replace$(formellang, "6 - Gesamtbestellungen", "6", , , vbTextCompare)
    10. Else
    11. sFormel = formellang
    12. End If
    14. Debug.Print sFormel & "(" & Len(sFormel) & ")"
    16. If kurz = 1 Then
    17. ThisWorkbook.Sheets("6 - Gesamtbestellungen").Name = "6"
    18. End If
    20. Range("W3").FormulaArray = sFormel
    21. Range("w3").AutoFill Destination:=Range("W3:W9"), Type:=xlFillDefault
    23. If kurz = 1 Then
    24. ThisWorkbook.Sheets("6").Name = "6 - Gesamtbestellungen"
    25. End If
    26. End Sub


    Für mich ist das jetzt gelöst. Wenn Du magst und weiterforschst und dabei herausfindest, dass es z.B. an einem Zeichen im Namen lag, dann bitte hier posten.
    Falls Du das Beispiel mit dem Umbenennen übernehmen möchtest, dann sollte noch geprüft werden, ob es evtl. schon ein Sheet '6' gibt.

    Gruß
    peterfido
    Gruß
    Peterfido

    Keine Unterstützung per PN!

    Hallo!

    Vielen Dank für deinen Post! Also... nein... es gab nicht noch ein Sheet "6" und... ich habe es jz wirklich sukzessive ausprobiert...

    Der maximale Blattname ergab bei mir 6 Zeichen! Also ich habe es in "6 - GB" umbenannt und das hat noch funktioniert... sobald ich es ein Zeichen länger gemacht habe... hat er mir wieder den Fehler geschmissen!!

    Somit, ist das auch von mir bestätigt... dass bei diesem Anwendungsfall... bei so einem Formelkonstrukt, nur maximal 6 Zeichen für den Blattnamen verwendet werden können...:

    Visual Basic-Quellcode

    1. Private Sub CommandButton2_Click()
    2. 'Range("W3").FormulaArray = "=Match(A3&B3&C3&D3&E3&F3&G3&H3&I3&J3,'6 -'!A:A&'6 -'!B:B&'6 -'!I:I&'6 -'!H:H&'6 -'!L:L&'6 -'!N:N&'6 -'!P:P&'6 -'!Q:Q&'6 -'!R:R&'6 -'!S:S,0)"
    3. 'Range("W3").FormulaArray = "=Match(A3&B3&C3&D3&E3&F3&G3&H3&I3&J3,'6 - G'!A:A&'6 - G'!B:B&'6 - G'!I:I&'6 - G'!H:H&'6 - G'!L:L&'6 - G'!N:N&'6 - G'!P:P&'6 - G'!Q:Q&'6 - G'!R:R&'6 - G'!S:S,0)"
    4. 'Range("W3").FormulaArray = "=Match(A3&B3&C3&D3&E3&F3&G3&H3&I3&J3,'6 - Ge'!A:A&'6 - Ge'!B:B&'6 - Ge'!I:I&'6 - Ge'!H:H&'6 - Ge'!L:L&'6 - Ge'!N:N&'6 - Ge'!P:P&'6 - Ge'!Q:Q&'6 - Ge'!R:R&'6 - Ge'!S:S,0)"
    5. 'Range("W3").FormulaArray = "=Match(A3&B3&C3&D3&E3&F3&G3&H3&I3&J3,'6 - Gesa'!A:A&'6 - Gesa'!B:B&'6 - Gesa'!I:I&'6 - Gesa'!H:H&'6 - Gesa'!L:L&'6 - Gesa'!N:N&'6 - Gesa'!P:P&'6 - Gesa'!Q:Q&'6 - Gesa'!R:R&'6 - Gesa'!S:S,0)"
    6. 'Range("W3").FormulaArray = "=Match(A3&B3&C3&D3&E3&F3&G3&H3&I3&J3,'6 - Ges'!A:A&'6 - Ges'!B:B&'6 - Ges'!I:I&'6 - Ges'!H:H&'6 - Ges'!L:L&'6 - Ges'!N:N&'6 - Ges'!P:P&'6 - Ges'!Q:Q&'6 - Ges'!R:R&'6 - Ges'!S:S,0)"
    7. Range("W3").FormulaArray = "=Match(A3&B3&C3&D3&E3&F3&G3&H3&I3&J3,'6 - GB'!A:A&'6 - GB'!B:B&'6 - GB'!I:I&'6 - GB'!H:H&'6 - GB'!L:L&'6 - GB'!N:N&'6 - GB'!P:P&'6 - GB'!Q:Q&'6 - GB'!R:R&'6 - GB'!S:S,0)"
    9. End Sub


    Nur als Information... wenn man die Zeichen mit Leerzeichen zählt kommt man auf 198 Zeichen...

    Am Blattnamen selbst, kann es allgemein gesehen, meiner Meinung nach nicht liegen... da ich viele Code-Teile habe, wo ich auf Sheets mit weitaus längeren Blattnamen verweise....

    Allgemein, an der Formellänge... denke ich... kanns auch nicht unbedingt liegen, da ich auch schon längere Formeln hatte...

    ABER...

    Ich hatte noch nie eine FormulaArray - Formel.... Und vielleicht gibt's bei FormulaArray ja tatsächlich eine Beschränkung, von <200 Zeichen oder so.... - Wer weiß...?!

    Vl. weiß ja jemand wirklich noch was dazu...

    ABER das sind soweit meine findings.... ;)

    Vielen Dank nochmals!

    LG Tim


    Warum das so ist verstehe ich zwar leider nach wie vor nicht... aber ja...

    Das tatsächliche Limit liegt bei 255 Zeichen.

    support.microsoft.com/de-de/kb/213181
    Dieses Problem tritt auf, wenn Sie versuchen, eine Formel zu übergeben, die mehr als 255 Zeichen enthält und verwenden die FormulaArray-Eigenschaft in Visual Basic for Applications.

    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

Facebook
Telefonspende