Gruppe
DatumZeit
Problem
Über eine benutzerdefinierte Funktion soll ermittelt werden, zu welcher Stunde wieviel Mitarbeiter anwesend sind.
StandardModule: basFunctions
Function MA(rngAll As Range, iHour As Integer)
Dim rng As Range
Dim iCount As Integer
For Each rng In rngAll.Cells
If rng.Interior.ColorIndex = 6 And _
Hour(Cells(1, rng.Column).Value) = iHour And _
rng.Offset(0, 1).Interior.ColorIndex = 6 Then
If Hour(Cells(1, rng.Column - 1).Value) <> iHour Or _
rng.Offset(0, -1).Interior.ColorIndex <> 6 Then
iCount = iCount + 1
End If
End If
Next rng
MA = iCount
End Function
Function MA1(rngAll As Range, iHour As Integer)
Dim iRow As Integer, iCol As Integer, iCount As Integer
iCol = WorksheetFunction.CountA(Rows(1)) - 1
For iRow = 2 To rngAll.Rows.Count
If iHour / 24 >= Cells(iRow, iCol).Value And _
iHour / 24 < Cells(iRow, iCol + 1).Value Then
iCount = iCount + 1
End If
Next iRow
MA1 = iCount
End Function