![]() |
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 SubGruß von Luschi
![]() ![]() |
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 SubEigentliches 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.
![]() ![]() |
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
.. , - ...
![]() |