Microsoft Excel

Herbers Excel/VBA-Archiv

Matrixformel mit Makro/VBA kopieren

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"