Die Excel Wühlkiste

Kalenderwoche in VBA errechnen

Hier wird eine Variante der Tabellenformel "SumProduct" eingesetzt.
37987 ist die laufende Zahl für den 01.01.2004.
"+ 10" benötigt man, damit das Programm für den 05.01.2004 die Kalenderwochoche 2 erkennt usw.
"/7" dürfte eigentlich klar sein, und "Int" schneidet die Nachkommastellen der Division "/7" ab.
Woche 1 ist die Woche, in die der 01.01.2004 fällt. Für andere Jahre muss die Formel angepasst werden.
Man könnte auch möglicherweise "DateDiff" benutzen. Das funktioniert auch tadellos in einem Makro. Nur habe ich es leider nicht geschafft, diese Anweisung innerhalb der "Evaluate" - Funktion ans Laufen zu bekommen. Darum auch die kleine Formel "(37987+10)/7".
Es ist vielleicht ein wenig lästig, das Makro am Anfang eines neuen Jahres anpassen zu müssen. Dennoch bevorzuge ich diesen Code hier wegen der Kürze der "SumProduct" - Tabellenfunktion. Automatic-Freaks können den Code ja auch noch für die Anpassung am Jahresanfang überarbeiten, wenn's ihnen Spass macht. :-)


Option Explicit

Sub Kalenderwoche()
Dim I As Integer ' Woksheets-Zähler
Dim Monat As Integer ' Monate von 1-12
Dim Woche As Integer ' Nummer der Kalenderwoche
Dim B As String ' Name des zu behandelnden Worksheets

Worksheets(Worksheets.Count).Select
Range("C3").Select

For I = 1 To Worksheets.Count - 1
B = Sheets(I).Name

For Woche = 1 To 52
Cells(Woche + 2, I + 3).Value = _
Evaluate("=SumProduct((Int((" & B & "!G2:G21 - 37987 + 10) / 7)" & _
"=" & Woche & ")*1)")
Next Woche

'    Monate auswerten:
'    For Monat = 1 To 12
'       Cells(Monat + 2, I + 3).Value = _
'       Evaluate("=SumProduct ((month(" & B & "!G2:G21)" & _
'       "=" & Monat & ")*1)")
'    Next Monat


Next I

' Summen für gesamt-Österreich:

For Woche = 1 To 52
Cells(Woche + 2, 10).Value = _
WorksheetFunction.Sum(Range(Cells(Woche + 2, 4), _
Cells(Woche + 2, 9)))
Next Woche

MsgBox ("Monatsauswertung abgeschlossen")

End Sub
Die Excel Wühlkiste
Valid HTML 4.01 Strict
letzte Aktualisierung: 13.02.2009
Autor: Hubert Scheidgen / 04.02.2009
W3C CSS-Validierungsservice