Herbers Excel/VBA-Archiv

Matrixformel mit Makro/VBA kopieren

• Matrixformel mit Makro/VBA kopieren von Dennis Menger vom 09.10.2014 07:31:15
• AW: Matrixformel mit Makro/VBA kopieren - von Dennis Menger am 09.10.2014 07:54:17
  • AW: Matrixformel mit Makro/VBA kopieren - von Luschi am 09.10.2014 08:50:25
  • Formelproblem - von ChrisL am 09.10.2014 14:29:24
• nur zur Formel ... - von neopa C (paneo) am 09.10.2014 17:44:26

Betrifft: Matrixformel mit Makro/VBA kopieren von: Dennis Menger
Geschrieben am: 09.10.2014 07:31:15

Hallo zusammen,

ich habe eine Matrixformel erstellt, die mir in der angehängten Datei eine Summe abhängig von einem Datum berechnet. Die Matrixformel funktioniert perfekt.

Mit einem Makro möchte ich jetzt eine neue Gruppe in der Datei anlegen, d.h. die Zeilen 2 bis 11 sollen komplett kopiert werden und unten drunter eingefügt werden. Das funktioniert auch schon mit dem Makro.

Allerdings befindet sich in Spalte HF jeweils die Matrixformel, die aber nicht so eingefügt wird wie ich es gerne hätte.

Nachstehend einmal der Code, der im Makro hinterlegt ist.

Wie kann ich die Matrixformel kopieren und einfügen, dass immer sofort der richtige Bezug drinnen ist. Hier einmal die Matrixformel.

{=SUMME(((TEXT(CB2:CX2;"MMJJ")=TEXT(HE5;"MMJJ"))*ZEILE(INDIREKT("1:" &ANZAHL2(A6:A11)))^0) *INDIREKT("CB6:CX$" &ANZAHL2(A6:A11)+5)) }

Die fettmarkierten Bereiche in der Formel werden nicht so eingefügt wie ich es gerne hätte. Oder kann man die Matrixformel anders schreiben?

Vielen Dank für eure Hilfe.

Gruß
Dennis

Sub Makro3()
'
' Makro3 Makro
'

'
    Rows("2:11").Select
    Selection.Copy
    Rows("12:12").Select
    Selection.Insert Shift:=xlDown
    ActiveWindow.SmallScroll Down:=6
    Range("A13:A21").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("D16:Z21").Select
    Selection.ClearContents
    Range("E13:F13").Select
    Selection.ClearContents
    Range("A12").Select
End Sub

Betrifft: AW: Matrixformel mit Makro/VBA kopieren von: Dennis Menger
Geschrieben am: 09.10.2014 07:54:17

https://www.herber.de/bbs/user/93041.xlsm

Betrifft: AW: Matrixformel mit Makro/VBA kopieren von: Luschi
Geschrieben am: 09.10.2014 08:50:25

Hallo Dennis,

für Zelle 'HF5' geht das so:

Sub Anwesenheit_HF5()
    Dim s As String
    s = "=SUM(((TEXT(CB2:CX2,""MMJJ"")=TEXT(HE5,""MMJJ""))" & _
        "*ROW(INDIRECT(""1:"" & COUNTA(A6:A11)))^0)*" & _
        "INDIRECT(""CB6:CX$"" & COUNTA(A6:A11)+5))"
    Range("Anwesenheit!HF5").FormulaArray = s
End Sub

Gruß von Luschi
aus klein-Paris

Betrifft: Formelproblem von: ChrisL
Geschrieben am: 09.10.2014 14:29:24

Hi Dennis

Kleine Abkürzung fürs Makro

Sub Makro3()
Rows("2:11").Copy
Rows("12:12").Insert Shift:=xlDown
Range("A13:A21,D16:Z21,E13:F13").ClearContents
End Sub

Eigentliches Problem ist aber die Formel zu dynamisieren, so dass sie sich beim Einfügen und Verschieben des Bereichs nach unten anpasst. Hierbei ist der Stolperstein Indirekt mit dem fixen String.

Nun, ich bin nicht der Formel-End-Boss :) aber grundsätzlich lässt sich die Formel auch ohne Array gestalten:

=SUMMENPRODUKT((MONAT(CB2:CX2)=MONAT(HE5))*(JAHR(CB2:CX2)=JAHR(HE5))*(CB6:CX11))

Problem sind die "" leeren Zellen. Normalerweise geht es so
=SUMMENPRODUKT((MONAT(CB2:CX2)=MONAT(HE5))*(JAHR(CB2:CX2)=JAHR(HE5));(CB6:CX11))
aber diesmal bekomme ich auch einen Fehler. :)

Was jetzt kommt ist nicht wirklich eine Lösung, darum erlaube ich mir die Frage wieder auf offen zu stellen. Aber im Bereich CB6:CX11 die Formel
=WENN($A9="";"";Formel)
schreib einfach eine Null rein
=WENN($A9="";0;Formel)
Dann bist Du die Probleme los

cu
Chris

Betrifft: nur zur Formel ... von: neopa C (paneo)
Geschrieben am: 09.10.2014 17:44:26

Hallo Dennis,

... ich hab mir jetzt nur Deine Formel angesehen und den Hinweis von ChrisL.

Der Formelteil *ZEILE(INDIREKT("1:" &ANZAHL2(A6:A11)))^0) ist überflüssig, da Du ja anschließend sowieso noch mit der Matrix aus dem Formelteil *INDIREKT("CB6:CX$" &ANZAHL2(A6:A11)+5) multiplizierst.

Und INDIREKT() würde ich ganz aus dem Spiel lassen und deshalb den zweiten Formelteil durch ($CB$6:INDEX($CX:$CX;ANZAHL2(A6:A11)+5)) ersetzen. Teste mal.


Gruß Werner
.. , - ...

Beiträge aus den Excel-Beispielen zum Thema "Matrixformel mit Makro/VBA kopieren"