Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.06.2025 22:11:12
24.06.2025 21:29:43
24.06.2025 19:48:50
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Matrixformel mit Makro/VBA kopieren

Matrixformel mit Makro/VBA kopieren
09.10.2014 07:31:15
Dennis
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

Anzeige

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Matrixformel mit Makro/VBA kopieren
09.10.2014 08:50:25
Luschi
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

Anzeige
Formelproblem
09.10.2014 14:29:24
ChrisL
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

Anzeige
nur zur Formel ...
09.10.2014 17:44:26
neopa
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
.. , - ...
Anzeige

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige