Option Explicit
Public Sub AdjustSTAndOTHours(Optional sheet As Worksheet)
Dim rng As Range
Dim rngTotals As Range
Dim lr As Long
Dim begRow As Long
Dim TotalHours, TotalSTHours, TotalOTHours As Integer
Dim DailyHours As Integer
Dim STHours As Integer
Dim OTHours As Integer
Dim othoursdiff As Integer
Dim SumOTHours As Integer
Dim dy, c, r As Integer
Dim tmp
Dim msgstr As String
If sheet Is Nothing Then
Set sheet = ActiveSheet
End If
lr = sheet.Range("A" & Rows.Count).End(xlUp).Row - 1
begRow = WorksheetFunction.Match("No.", sheet.Range("A:A"), 0) + 1
TotalSTHours = 0
TotalOTHours = 0
msgstr = ""
For r = begRow To lr
Set rng = sheet.Range("F" & r & ":X" & r)
Set rngTotals = sheet.Range("Y" & r & ":Z" & r)
' For c = 1 To 15 Step 3
' Debug.Print rng(1, c), rng(1, c + 1)
' Next c
TotalHours = WorksheetFunction.SumIf(rng, ">0")
'Are ST and OT hours already done?
If rngTotals(1, 2) > 0 Then 'it appears OT hours are already calculated
msgstr = msgstr & ", " & r
Else
If TotalHours > 40 Then
TotalSTHours = 40
TotalOTHours = TotalHours - 40
Else
TotalSTHours = TotalHours
TotalOTHours = 0
End If
SumOTHours = 0
If TotalHours > 40 Then
For dy = 1 To 7 'monday thru sunday
If dy < 6 Then
c = (dy - 1) * 3 + 1
Else
c = 15 + (dy - 6) * 2 + 1
End If
If rng(1, c) > 8 And SumOTHours < TotalOTHours Then
STHours = 8
OTHours = rng(1, c) - 8
SumOTHours = SumOTHours + OTHours
If SumOTHours > TotalOTHours Then
othoursdiff = (SumOTHours - TotalOTHours)
STHours = STHours + othoursdiff
OTHours = OTHours - othoursdiff
SumOTHours = SumOTHours - othoursdiff
End If
rng(1, c) = STHours
rng(1, c + 1) = OTHours
' Debug.Print STHours, OTHours, SumOTHours
Else
STHours = rng(1, c)
' Debug.Print STHours
End If
Next dy
End If
Set rng = sheet.Range("Y" & r & ":Z" & r)
' rng(1, 1) = TotalSTHours 'do not write these values there are formulas in the cells
' rng(1, 2) = TotalOTHours 'do not write these values there are formulas in the cells
If TotalOTHours <> SumOTHours Then
MsgBox "Row: " & r & "OT Hours mismatch. Timesheet OT hours = " & SumOTHours & _
", Total OT Hours = " & TotalOTHours
End If
End If
Next r
If Len(msgstr) > 0 Then
MsgBox "ST and OT hours already calculated for row(s): " & Mid(msgstr, 3)
End If
End Sub
