Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1292to1296
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Teilsummen per VBA bei 2 Kriterien

Teilsummen per VBA bei 2 Kriterien
27.12.2012 21:37:22
Mexsalem
Hallo,
ich möchte gerne per Makro zwei Teilsummen bilden (siehe Beispieldatei).
https://www.herber.de/bbs/user/83151.xls
Anhand gleicher Nummernkreise in Spalte B sollen in Spalte L die Teilsummen aller Beträge (Spalte K) gebildet werden, die dem Kriterium der Spalte C (entweder "A" oder "B") entsprechen.
Die Anzahl der Spalteneinträge variert, beginnen aber immer mit Zeile 2 und sind zeilenweise vollständig (keine Lücken).
Kann mir jemand bitte helfen ?
Gruß
Mexsalem

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Teilsummen per VBA bei 2 Kriterien
27.12.2012 23:09:27
Richi
Hallo Mexsalem
wieso VBS, das geht mit SummeWenn und einer Hilfsspalte ganz gut.
Ich habe D als Hilsfspalte genommen und die Summewenn in A
https://www.herber.de/bbs/user/83153.xls
Liebe Grüsse
Richi

AW: ohne Hilfsspalte (aber auch ohne VBA)
28.12.2012 00:02:26
Mexsalem
Hallo richi, Hallo serge,
besten Dank erstmal für eure Antworten. Ohne Hilfspalte ist hier schonmal nicht schlecht (Lösung von serge), da keine Spalte in der Originaltabelle ergänzt werden soll.
Nur bleibt die Frage offen, da ich hier ein Makro bräuchte, das ich in ein bestehendes Makro integrieren möchte (dieses Makro liefert mir die Teildaten in den genannten Spalten).
Kann mir jemand eventuell noch einen VBA-Vorschlag machen ?
Liebe Grüße
Mexsalem

Anzeige
AW: ohne Hilfsspalte (aber auch ohne VBA)
28.12.2012 00:17:19
Mexsalem
Hallo serge,
habe leider gerade noch einen Fehler in der Formel bemerkt. Der Fehler tritt auf, wenn Nummern in Spalte B nur einmal vorkommen, dann addiert er diesen einzeilen Eintrag nicht korrekt.
Gruß Mexsalem

AW: Formel korrigiert
28.12.2012 08:29:41
Mexsalem
Hallo serge,
habe deine Formel auf die Spalten meiner Beispielmappe angepasst und von
=WENN(UND(B2&C2=B1&C1;B3&C3B2&C2);SUMME(K$2:K2)-SUMME(L1:L$2);"")
auf
=WENN(UND(B2&C2=B1&C1;B3&C3B2&C2);SUMME(K$2:K2)-SUMME(L1:L$2);
WENN(UND(B2&C2B1&C1;B3&C3B2&C2);SUMME(K$2:K2)-SUMME(L1:L$1);""))
erweitert - damit werden, wie von mir gewünscht, auch die einzeiligen Einträge korrekt berechnet.
Vielen Dank für deine Idee.
Ich kann somit diese Formellösung via VBA in meinen Code einpflanzen.
Gruß
Mexsalem

Anzeige
AW: Teilsummen per VBA bei 2 Kriterien
28.12.2012 09:12:24
Beverly
Hi,
setze auf die Spalten A:K einen Autofilter, dann kannst du mit folgendem Code die Teilsummen eintragen lassen:
Sub TeilsummeEintragen()
Dim lngLetzte As Long
Dim objNummer As Object
Dim lngZeile As Long
Dim arrBereich
Dim arrNummer
Application.ScreenUpdating = False
Range("B1:K1").AutoFilter Field:=1
Range("B1:K1").AutoFilter Field:=2
lngLetzte = Columns(2).Find(What:="*", _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set objNummer = CreateObject("Scripting.Dictionary")
arrBereich = Range(Cells(2, 2), Cells(lngLetzte, 2))
For lngZeile = LBound(arrBereich) To UBound(arrBereich)
objNummer(arrBereich(lngZeile, 1)) = 0
Next
arrNummer = objNummer.keys
For lngZeile = 0 To UBound(arrNummer)
Range("B1:K1").AutoFilter Field:=1, Criteria1:=arrNummer(lngZeile)
Range("B1:K1").AutoFilter Field:=2, Criteria1:="A"
Cells(IIf(IsEmpty(Cells(Rows.Count, 2)), _
Cells(Rows.Count, 2).End(xlUp).Row, Rows.Count), 12) = _
Application.Subtotal(9, Columns("K"))
Range(Cells(2, 2), Cells(lngLetzte, 2)).AutoFilter Field:=2, Criteria1:="B"
Cells(IIf(IsEmpty(Cells(Rows.Count, 2)), _
Cells(Rows.Count, 2).End(xlUp).Row, Rows.Count), 12) = _
Application.Subtotal(9, Columns("K"))
Range("B1:K1").AutoFilter Field:=1
Range("B1:K1").AutoFilter Field:=2
Next lngZeile
Application.ScreenUpdating = True
Set objNummer = Nothing
End Sub


Anzeige
AW: Teilsummen per VBA bei 2 Kriterien
28.12.2012 13:05:50
Mexsalem
Hallo Beverly,
danke für deinen VBA-Code. Leider passiert nicht das Erwartete. Ich erhalte - trotz des gesetzten Autofilters in den Spalten A-K - nur in L1 eine Null, keine weiteren Teilsummen.
Ich wollte die Formellösung von serge in meinen Code einbauen, verzweifle aber daran, dass mir Excel die Formel nicht einsetzten will. Hintergrund ist, dass die Tabelle eventuell noch sortiert werden soll und die Teilsummen dann auch bei neuer Sortierung angezeigt werden soll (Vorteil Formel).
In L2 habe ich diese Formel eingesetzt :
=WENN(UND(B2&C2=B1&C1;B3&C3B2&C2);SUMME(K$2:K2)-SUMME(L1:L$2);
WENN(UND(B2&C2B1&C1;B3&C3B2&C2);SUMME(K$2:K2)-SUMME(L$1:L1);""))
Mit msgbox Range("L2").formula erhalte ich die englische Formelübersetzung :
=IF(AND(B2&C2=B1&C1,B3&C3B2&C2),SUM(K$2:K2)-SUM(L1:L$2),
IF(AND(B2&C2B1&C1,B3&C3B2&C2),SUM(K$2:K2)-SUM(L$1:L1),""))
Möchte ich nun diese Formel per VBA in der Variante Formula einsetzen, bekomme ich einen "Laufzeitfehler 1004 Anwendungs- oder objektdefinierter Fehler":
Sub SetMyFormula()
Dim Formel
Formel = "=IF(AND(B2&C2=B1&C1,B3&C3B2&C2),SUM(K$2:K2)-SUM(L1:L$2), _
IF(AND(B2&C2B1&C1,B3&C3B2&C2),SUM(K$2:K2)-SUM(L$1:L1),""))"
Range("L2").Formula = Formel
End Sub
Ich verwende noch Excel 2003 und dachte es gibt hier Probleme bei englischen Formeln, aber auch die deutsche Variante FormulaLocal bringt mir den Laufzeitfehler.
Sub SetMyFormula()
Dim Formel
Formel = "=WENN(UND(B2&C2=B1&C1;B3&C3B2&C2);SUMME(K$2:K2)-SUMME(L1:L$2); _
WENN(UND(B2&C2B1&C1;B3&C3B2&C2);SUMME(K$2:K2)-SUMME(L$1:L1);""))"
Range("L2").FormulaLocal = Formel
End Sub
Wenn ich die englische Formel direkt in die Zelle (F2) einsetze, meckert er mir, da die Kommata stören. Ersetze ich diese druch Semikolon, nimmt er zwar die Formel an, gibt aber #Name? aus.
Nehme ich die Variante FormulaR1C1
Sub SetMyFormula()
Dim Formel
Formel = "=IF(AND(RC[-10]&RC[-9]=R[-1]C[-10]&R[-1]C[-9],R[1]C[-10]&R[1]C[-9]RC[-10]&RC[-9]),  _
SUM(R2C[-1]:RC[-1])-SUM(R[-1]C:R2C), IF(AND(RC[-10]&RC[-9]R[-1]C[-10]&R[-1]C[-9],R[1]C[-10]&R[1]C[-9]RC[-10]&RC[-9]), _
SUM(R2C[-1]:RC[-1])-SUM(R1C:R[-1]C),""""))"
Range("L2").FormulaR1C1 = Formel
End Sub
funktioniert die Formeleintragung in Zelle L2 (zeigt diese als deutsche Formel in A1-Schreibweise an).
Ich benötige jedoch die Variante Formula oder FormalLocal, damit ich die Spalten variabel ansprechen kann. Hat hier jemand einen Tipp für mich, wie das mit Formula / FormulaLocal funktionieren könnte ?
Spalte B = ColNummer
Spalte C = ColArt
Spalte K = ColBetrag
Spalte L = ColSumme
Gruß
Mexsalem

Anzeige
AW: Teilsummen per VBA bei 2 Kriterien
28.12.2012 13:39:19
Beverly
Hi,
ich kann leider nicht nachvollziehen, weshalb in deiner Arbeitsmappe mit meinem Code angeblich nichts passieren soll - in der Arbeitsmappe, die du hochgeladen hattest, funktioniert alles wie gewünscht (gestestet in Excel2003 und Excel2010).
https://www.herber.de/bbs/user/83161.xls


AW: Teilsummen per VBA bei 2 Kriterien
28.12.2012 16:08:34
Mexsalem
Hallo Beverly,
ich habe den Code nochmal in meine ursprüngliche Beispielmappe eingefügt und den Autofilter bei den Spalten A-K aktiviert, wie du geschrieben hattest :
setze auf die Spalten A:K einen Autofilter, dann kannst du mit folgendem Code die Teilsummen eintragen lassen:
Ergebnis : leider nur in L1 eine Null.
Anhand deiner beigefügten Beispielmappe habe ich dann den Unterschied erkannt. Es darf nur der Autofilter in den Spalten B-K aktiv sein, dann funktioniert dein Code.
Gruß
Mexsalem
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige