18.05.2020, 12:30
Hallo zusammen!
In einer Excel Datei habe ich eine Art Kalender und möchte in bestimmten Zeilen eine Zwischensumme bilden. Hierfür habe ich neben dem Kalender eine Hilfsspalte "Zeilentyp", in der für jede Zeile definiert wird, ob es eine Zwischenzeile ist ("Überschrift" --> Summe bilden), oder eine ganz Normale Wert Zeile ist ("Text" --> Zeile hat einfache Werte, keine Formel).
Die als "Überschrift" in Spalte B gekennzeichneten Zeilen werden von mir mittels folgenden VBA Code angesteuert und mit einer Formel versehen:
Die Idee ist also folgende:
A. Der Kalender wird auf die Überschriftenzeilen heruntergefiltert
B. In der ersten Spalte (D) des Kalenders wird die Formel für jede Überschriftenzeile per VBA eingefügt
C. Der verwendete Bereich in Spalte D wird per VBA markiert und bis zum rechten Ende des Kalenders gezogen.
D. Anschließend wird der Filter wieder entfernt
Am Ende soll in den Überschriftenzeilen ("Überschrift") also eine Formel stehen, welche die Untergeordneten Elemente aufsummiert und in den Zwischenzeilen ("Text") nur die jeweiligen einfachen Werte.
Leider funktioniert das nicht ganz so wie ich es mir vorstelle.
Mit meiner aktuellen Vorgehensweise werden auch die Werte in den Zwischenzeilen mit der Kennzeichnung "Text" rüberkopiert, das Filtern nach Überschriftenzeilen und dass anschließende nach rechts kopieren nimmt also auch die Werte der Zwischenzeilen mit.
Deshalb meine Fragen:
Vielleicht versuche ich hier grade auch etwas "von hinten durch die Brust ins Auge" umzusetzen, für eventuelle andere Denkansätze wäre ich sehr dankbar. Falls es zum Beispiel möglich ist die Kalkulation der "Überschriftenzeile" in VBA vorzunehmen und dann den ermittelten wert einfach als Wert in die Überschriftenzeile einzutragen, könnte ich mir das ermitteln der Formelzeilen und das Eintragen der Formeln sparen.
[attachment=31802]
Vielen Dank & Grüße
In einer Excel Datei habe ich eine Art Kalender und möchte in bestimmten Zeilen eine Zwischensumme bilden. Hierfür habe ich neben dem Kalender eine Hilfsspalte "Zeilentyp", in der für jede Zeile definiert wird, ob es eine Zwischenzeile ist ("Überschrift" --> Summe bilden), oder eine ganz Normale Wert Zeile ist ("Text" --> Zeile hat einfache Werte, keine Formel).
Die als "Überschrift" in Spalte B gekennzeichneten Zeilen werden von mir mittels folgenden VBA Code angesteuert und mit einer Formel versehen:
Code:
Private Sub CommandButton1_Click()
'SCHRITT A
ActiveSheet.Range("$B$4:$C$18").AutoFilter Field:=1, Criteria1:=Array("Überschrift"), Operator:=xlFilterValues
'SCHRITT B
Dim i As Long
LetzteZeile = Worksheets("Tabelle1").UsedRange.Rows.Count
For i = 5 To LetzteZeile
With Tabelle1
If .Cells(i, 2).Value = "Überschrift" Then
.Cells(i, 4).Formula = "=SUMIF($C" & i + 1 & ":$C" & i + 13 & ",$C" & i & ",D" & i + 1 & ":D" & i + 13 & ")"
End If
End With
Next i
'SCHRITT C
Range("D5:D18").Select
Selection.AutoFill Destination:=Range("D5:N18"), Type:=xlFillDefault
'SCHRITT D
ActiveSheet.Range("$B$4:$C$18").AutoFilter Field:=1, Criteria1:=Array("Überschrift", "Text"), Operator:=xlFilterValues
End SubDie Idee ist also folgende:
A. Der Kalender wird auf die Überschriftenzeilen heruntergefiltert
B. In der ersten Spalte (D) des Kalenders wird die Formel für jede Überschriftenzeile per VBA eingefügt
C. Der verwendete Bereich in Spalte D wird per VBA markiert und bis zum rechten Ende des Kalenders gezogen.
D. Anschließend wird der Filter wieder entfernt
Am Ende soll in den Überschriftenzeilen ("Überschrift") also eine Formel stehen, welche die Untergeordneten Elemente aufsummiert und in den Zwischenzeilen ("Text") nur die jeweiligen einfachen Werte.
Leider funktioniert das nicht ganz so wie ich es mir vorstelle.
Mit meiner aktuellen Vorgehensweise werden auch die Werte in den Zwischenzeilen mit der Kennzeichnung "Text" rüberkopiert, das Filtern nach Überschriftenzeilen und dass anschließende nach rechts kopieren nimmt also auch die Werte der Zwischenzeilen mit.
Deshalb meine Fragen:
- Wie kann ich ich die Formeln der "Überschriften Zeilen" auf alle anderen Spalten des Kalenders ausbreiten, ohne, dass ich die Werte der Zwischenzeilen mitkopiere?
- Der Kalender soll am Ende sehr variabel sein, d.h. um die Datei schlussendlich klein zu halten würde ich das ausweiten der Formeln (Schritt C) auf den Kalender nur auf den absolut notwendigen Bereich beschränken. Der notwendige Bereich ist in Zeile 3 markiert. Ist der Zellwert der jeweiligen Spalte = 1, dann ist diese Spalte relevant, ansonsten nicht. In diesem Beispiel soll die Formel also nur bis Spalte "J" eingetragen werden. Da sich dieser je nach verwendeten Datensatz ändern kann, muss dies vermutlich auch per VBA ermittelt werden.
Vielleicht versuche ich hier grade auch etwas "von hinten durch die Brust ins Auge" umzusetzen, für eventuelle andere Denkansätze wäre ich sehr dankbar. Falls es zum Beispiel möglich ist die Kalkulation der "Überschriftenzeile" in VBA vorzunehmen und dann den ermittelten wert einfach als Wert in die Überschriftenzeile einzutragen, könnte ich mir das ermitteln der Formelzeilen und das Eintragen der Formeln sparen.
[attachment=31802]
Vielen Dank & Grüße