VBA Summen bilden (vgl. "Summewenns")

[diving_excel]

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:

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 Sub

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:

1. Wie kann ich ich die Formeln der "Überschriften Zeilen" auf alle anderen Spalten des Kalenders ausbreiten, ohne, dass ich die Werte der Zwischenzeilen mitkopiere?
   
2. 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.
   

Noch ein Hinweis: Die Original Datei besteht aus einigen Tausend Zeilen mit mindestens 120 Spalten für den Kalender (1 Spalte / Monat). Sie ist also sehr groß und dementsprechend träge, weshalb ich es möglichst vermeiden möchte von vornherein die Formeln für die Summenzeilen einzutragen (je nach dem welche Daten als Grundlage reingeladen werden, werden mehr oder weniger Spalten benötigt). Erschwerend kommt noch hinzu, dass ich nicht von Anfang an sagen kann, ob eine Formel in der jeweiligen Zeile benötigt wird, oder nicht. Das kommt immer ganz auf den Datensatz an, den ich in die Datei reinlade. Deshalb auch der VBA Code zum ermitteln welche Zeilen nun eine Formel benötigen.


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.


  beispiel 1.xlsm (Größe: 26,14 KB / Downloads: 7)

Vielen Dank & Grüße

[shift-del]

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.

Man kann eine komplizierte Sache noch komplizierter machen oder man schmeißt das Konzept weg und erstellt eine Datensatzliste. Auswertung mit Pivot.
Keine Formeln. Kein VBA.

[Raoul21]

Hi

mit einer Schleife vertikal von Überschrift zu Überschrift und horizontal über alle Einsen in 3 wäre das per Makro möglich.

Gruß, Ralf

[diving_excel]

Sorry für die Späte Antwort.

Normalerweise würde ich das auch über eine Pivot Tabelle lösen. Mein Problem ist aber, dass ich verschiedene Einheiten in der selben Tabelle habe. Einmal € Werte, dann Stunden Werte und dann daraus resultierende Gesamtkosten (€ + H * Stundensatz).
Typischerweise sieht die Struktur dann so aus:

AAA Kosten 10 €
AAA Stunden 5 H (à 20 €)
AAA Gesamtkosten 110€ (10 € + 5 H * 20 €)
....
BBB Kosten (€)
BBB Stunden (H)
BBB Gesamtkosten (€)

....
ZZZ Kosten(€)
ZZZ Stunden (H)
ZZZ Gesamtkosten(€)


Da unterschiedliche Einheiten vorkommen kann ich die automatische Summenbildung einer Pivot Tabelle nicht nutzen, da sonst € Werte mit Stunden Werte zusammengelegt werden.

Mein aktueller Workaround ist, dass ich eine Pivot Tabelle nutze um die Grundwerte abzubilden(Kosten und Stunden) und dann für die Gesamtkosten eine Formel per VBA eintragen lasse. Für die Zeile der Gesamtkosten ist dies eine Summenprodukt Formel.

Das ganze funktioniert jetzt in etwa so wie ich mir das vorgestellt habe, allerdings ist es recht langsam.

Gruß und noch einen schönen Abend