' **********************************************************************
' Modul: Modul2 Typ: Allgemeines Modul
' **********************************************************************
Option Explicit
Sub mitarbeiterTage()
Dim lngMonth As Long, lngLast As Long, lngRow As Long, lngCol As Long, lngLastCol As Long
Dim varTemp As Variant, varColor As Variant, varRet As Variant, lngWeek As Long, lngDay As Long
Dim objData As Worksheet
Set objData = Worksheets("Daten")
For lngMonth = 1 To 1 '2
With Sheets(Format(DateSerial(objData.Range("S3"), lngMonth, 1), "MMMM"))
lngLast = Application.Max(2, .Cells(.Rows.Count, 1).End(xlUp).Row)
lngLastCol = 4 + Day(DateSerial(objData.Range("S3"), lngMonth + 1, 0))
varTemp = .Range(.Cells(1, 3), .Cells(lngLast, lngLastCol))
varColor = .Range(.Cells(1, 3), .Cells(1, lngLastCol)).Interior.Color
For lngRow = 2 To UBound(varTemp, 1)
varRet = Application.Match(varTemp(lngRow, 1), objData.Columns(3), 0)
If IsNumeric(varRet) Then
For lngCol = 3 To UBound(varTemp, 2)
If isHolyday(varTemp(1, lngCol)) = "" Then
lngDay = Weekday(varTemp(1, lngCol), vbMonday)
lngWeek = (DINKwoche(varTemp(1, lngCol)) Mod 2 = 0) * -5
If objData.Cells(varRet, 4 + lngDay + lngWeek) = "x" Then
varTemp(lngRow, lngCol) = "x"
End If
End If
Next
End If
Next
.Range(.Cells(1, 3), .Cells(lngLast, lngLastCol)) = varTemp
End With
Next
Set objData = Nothing
End Sub
Private Function DINKwoche(ByVal Datum As Date) As Long
Dim tmp As Date
tmp = DateSerial(Year(Datum + (8 - Weekday(Datum)) Mod 7 - 3), 1, 1)
DINKwoche = (Fix(Datum - tmp - 3 + (Weekday(tmp) + 1) Mod 7) \ 7) + 1
End Function
Public Function isHolyday(ByVal datDate As Date) As String
Select Case datDate
Case DateSerial(Year(datDate), 1, 1): isHolyday = "Neujahr"
Case DateSerial(Year(datDate), 1, 6): isHolyday = "Hl. drei Könige"
Case Ostern(Year(datDate)) - 2: isHolyday = "Karfreitag"
Case Ostern(Year(datDate)): isHolyday = "Ostersonntag"
Case Ostern(Year(datDate)) + 1: isHolyday = "Ostermontag"
Case Ostern(Year(datDate)) + 39: isHolyday = "Christi Himmelfahrt"
Case Ostern(Year(datDate)) + 49: isHolyday = "Pfingstsonntag"
Case Ostern(Year(datDate)) + 50: isHolyday = "Pfingstmontag"
Case Ostern(Year(datDate)) + 60: isHolyday = "Fronleichnam"
Case DateSerial(Year(datDate), 8, 15): isHolyday = "Maria Himmelfahrt"
Case DateSerial(Year(datDate), 10, 3): isHolyday = "datdate der D. Einheit"
Case DateSerial(Year(datDate), 11, 1): isHolyday = "Allerheiligen"
Case DateSerial(Year(datDate), 12, 24): isHolyday = "Heiliger Abend"
Case DateSerial(Year(datDate), 12, 25): isHolyday = "1. Weihnachtstag"
Case DateSerial(Year(datDate), 12, 26): isHolyday = "2. Weihnachtstag"
Case DateSerial(Year(datDate), 12, 31): isHolyday = "Sylvester"
Case Else: isHolyday = ""
End Select
End Function
