AW: Datumsliste für bestimmten Zeitraum erstellen
13.01.2016 19:58:06
Sepp
Hallo Reimund,
in das Modul der Tabelle.
' **********************************************************************
' Modul: Tabelle1 Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim varMonth() As Variant, varDay() As Variant
Dim lngStart As Long, lngEnd As Long, lngI As Long, lngN As Long, LngC
On Error GoTo Errorhandler
Application.EnableEvents = False
If Not Intersect(Target, Range("A2:A3")) Is Nothing Then
If IsNumeric(Range("A2")) And IsNumeric(Range("A3")) Then
lngStart = Application.Min(Range("A2:A3"))
lngEnd = Application.Max(Range("A2:A3"))
Redim varMonth(1 To (lngEnd - lngStart + 1) * 12, 1 To 1)
Redim varDay(1 To UBound(varMonth, 1) * 53, 1 To 1)
For lngI = 1 To UBound(varMonth)
varMonth(lngI, 1) = DateSerial(lngStart, lngI + 1, 0)
For lngN = 1 To Day(DateSerial(lngStart, lngI + 1, 0))
If Weekday(DateSerial(lngStart, lngI, lngN), vbSunday) = 1 Then
LngC = LngC + 1
varDay(LngC, 1) = DateSerial(lngStart, lngI, lngN)
End If
Next
Next
Range("B2:C" & Me.Rows.Count) = ""
Range("B2").Resize(UBound(varDay, 1), 1) = varDay
Range("C2").Resize(UBound(varMonth, 1), 1) = varMonth
End If
End If
Errorhandler:
Application.EnableEvents = True
End Sub
Gruß Sepp