Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1632to1636
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
VBA Sumif Jahreszahl gleich
11.07.2018 14:51:46
Berg
Hallo allerseits,
folgendes Problem. In Spalte B werden über eine Userform ein Datum und in Spalte C bzw. D zwei zugehörige Zahlenwerte in Tabelle3 eingetragen. Nun möchte ich in Tabelle 2 diese Werte jeweils Summieren wenn die Jahreszahlen identisch sind. Ich weiß das ganze lässt sich über eine Pivottabelle lösen, aber ich muss das ganze via VBA-Code bewerkstelligen:
D.h. aus…
Tabelle3:
Spalte B Spalte C Spalte D
01.01.2018 1 1
01.02.2019 1 1
01.03.2019 2 2
01.04.2020 1 1
02.05.2020 1 1
… … ..
…Mach…
Tabelle2:
Spalte B Spalte C Spalte D
2018 1 1
2019 3 3
2020 2 2
Um eine Hilfe bin ich sehr dankbar. Vorab besten Dank
Gruß

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA Sumif Jahreszahl gleich
11.07.2018 15:59:24
Rob
Für Spalte C (D kannst du entsprechend anpassen):

Sub CountIfYear()
Dim r As Range
Dim Sum2018, Sum2019, Sum2020 As Integer
Sum2018 = 0
Sum2019 = 0
Sum2020 = 0
For Each r In Intersect(Tabelle1.UsedRange, Range("B:B"))
Select Case Right(r.Offset(0, -1), 4)
Case Is = "2018"
Sum2018 = Sum2018 + 1
Case Is = "2019"
Sum2019 = Sum2019 + 1
Case Is = "2020"
Sum2020 = Sum2020 + 1
End Select
Next r
With Tabelle2
.Range("B2") = Sum2018
.Range("B3") = Sum2019
.Range("B4") = Sum2020
End With
End Sub

AW: VBA Sumif Jahreszahl gleich
11.07.2018 16:02:00
Rob
Sorry, kleiner Fehler. Anstelle Sum2018 + 1 muss es heißen Sum2018 + r
Anzeige
Summenprodukt()
11.07.2018 16:02:45
ChrisL
Hi
Nur mal das Formelthema...
Sub t()
'=SUMMENPRODUKT((JAHR(B1:B10)=2019)*(C1:C10))
MsgBox Evaluate("SUMPRODUCT((YEAR(B1:B10)=2019)*(C1:C10))")
End Sub
cu
Chris
AW: Summenprodukt()
11.07.2018 19:34:49
Rob
Ist eine in VBA eingebettete Excel-Funktion streng genommen noch VBA? Weil wozu dann noch ein Makro schreiben, wenn Du das direkt über das Arbeitsblatt lösen kannst?
AW: Summenprodukt()
12.07.2018 08:54:54
ChrisL
Hi Rob
Die Sinnfrage stelle ich mir tatsächlich.
Aber ich wollte hauptsächlich darauf hinweisen, dass die im Titel erwähnte Funktion hier nicht funktioniert.
WorksheetFunction.SumIf =SUMMEWENN
WorksheetFunction.CountIf = ZÄHLENWENN
WorksheetFunction.SumProduct = SUMMENPRODUKT
Es ist nicht verboten, Tabellenfunktionen auch in VBA zu verwenden. Meist kommt man sogar auf eine schlankere Lösung, wenn man sich vorher überlegt, welches die geeignete Formel wäre und dann eine Transformation nach VBA vornimmt.
z.B.
Sub t()
Dim i As Long
For i = 2017 To 2020
Cells(i - 2016, 4) = Evaluate("SUMPRODUCT((YEAR(B1:B10)=" & i & ")*(C1:C10))")
next i
End Sub
cu
Chris
Anzeige
AW: Summenprodukt()
12.07.2018 09:16:20
Berg
Hallo Rob und ChrisL,
eine wirklich sehr schlanke und elegante Lösung. Genau das was ich gesucht habe. Vielen vielen Dank!
Viele Grüße
Berg
AW: Summenprodukt()
12.07.2018 09:22:23
Rob
Das einzige Problem bei der Lösung ist, dass sie nicht dynamisch ist. Wenn sich Deine Tabellen aber nicht in der Zeilenanzahl verändern, kann man natürlich damit leben.
AW: Summenprodukt()
12.07.2018 18:12:07
ChrisL
Hi
Die Sinnfrage bleibt, aber Dynamik kannst du haben ;)
Sub t()
Call MeinProgramm(2017, 2020)
End Sub

Private Sub MeinProgramm(intStart As Integer, intEnde As Integer)
Dim i As Integer
For i = intStart To intEnde
Cells(i - intStart + 1, 4) = _
Evaluate("SUMPRODUCT((YEAR(Bereichsname1)=" & i & ")*(Bereichsname2))")
next i
End Sub

cu
Chris
Anzeige
AW: Summenprodukt()
13.07.2018 11:22:01
Berg
Hallo Chris,
super, jetzt funktionierts sogar Dynamisch :) Danke nochmal fürs Nachhaken. Jetzt hätte ich nochmal eine Sache. Wenn ein Jahr in der Ausgangsliste die gruppiert werden soll fehlt dann funktioniert es nicht.
2018
2019
2021
Kann man noch was einbauen, sodass er alle Jahre aufschlüsselt und wenn ein Jahr fehlt (hier z.B. 2020) dann schreibt er den Wert "0". Geht das?
Dankeschöööön!!!
AW: Summenprodukt()
13.07.2018 12:42:37
ChrisL
Hi
Ist doch bereits so. Es werden alle Jahre von/bis durchlaufen und ggf. 0 ausgegeben.
cu
Chris
AW: Summenprodukt()
13.07.2018 13:39:11
Berg
Hi Chris,
danke nochmal. Ich erhalte immer #WERT! in den Zellen. Ich hab allerdings deinen Code mit Tabelle2.cells und tabelle3.evaluate ergänzt. Darf man das vll nicht?
Sub Schaltfläche2_Klicken()
Call MeinProgramm(2017, 2022)
End Sub
Private Sub MeinProgramm(intStart As Integer, intEnde As Integer)
Dim i As Integer
For i = intStart To intEnde
'Ich Starte in Zeile 8 wegen den Überschriften
Tabelle2.Cells(i - intStart + 8, 4) = _
Tabelle3.Evaluate("SUMPRODUCT((YEAR(C8:C15)=" & i & ")*(E8:E15))")
Next i
End Sub
Gruß
Anzeige
AW: Summenprodukt()
13.07.2018 14:45:24
ChrisL
Hi
ungetestet...
Private Sub MeinProgramm(intStart As Integer, intEnde As Integer)
Dim i As Integer
For i = intStart To intEnde
'Ich Starte in Zeile 8 wegen den Überschriften
Tabelle2.Cells(i - intStart + 8, 4) = _
Evaluate("SUMPRODUCT((YEAR(Tabell3!C8:C15)=" & i & ")*(Tabelle3!E8:E15))")
Next i
End Sub
cu
Chris
AW: Summenprodukt()
13.07.2018 15:01:11
Berg
Hi,
jetzt weis ich woran es liegt. Ein Formatproblem mit dem Datum! Aber völlig absurd. Ich gebe meine Daten (Datum und Wert) über eine Userform ein. Die speichere ich formatiert wie folgt:
Tabelle3.Cells(lZeile, 3).Value = CDate(TextBox2)
Tabelle3.Cells(lZeile, 3) = Format(TextBox2, "dd.mm.yyyy")
Wenn ich dann in die Zelle klicke sind die Werte als Datum bzw. Zahl formatiert hinterlegt.
Wenn ich dann dein Makro laufen lassen schreibt er #Wert!
Wenn ich aber das Datum direkt in die Zelle schreibe (ohne Userform) funktionert dein Makro genau so wie es soll. Häääää?
Anzeige
AW: Summenprodukt()
13.07.2018 15:26:48
Berg
Ja, es war ein Formatfehler vom Datum.
So klappts:
Tabelle3.Cells(lZeile, 3).Value = CDate(TextBox2)
Tabelle3.Cells(lZeile, 3).NumberFormat = "dd.mm.yyyy"
Chris, ein fettes Merci für deine Hilfe. Echt super!!!
VG

145 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige