Looping problem
 

Hi all,

I need to calculate straight-time, overtime, and doubletime hours for a two
week period (for each worker). The rules a Anything over 12 hours in a
day is doubletime. Anything over 8 hours in a day is overtime AND anything
over 40 hours in a week is overtime.

I have a worksheet with several rows (one per worker) and columns (one for
each day of the two week period). The sub successfully and accurately
calculates straight, over, and doubletime for the first row only. I need to
know how to change the sub so that the range changes to the next row for each
row that has entries.

Thanks in advance for any help provided. Here's the code:

Dim st As Long, ot As Double, dt As Double, AccST1 As Double
Dim AccOT As Double, AccDT As Double, AccST2 As Double
Dim wks As Worksheet
Dim Workers As Variant, Week1 As Variant, Week2 As Variant
Dim WorkerCounter As Long, Wk1Counter As Long, Wk2Counter As Long
Dim Rg2Add1 As Range, Rg2Add2 As Range, cell As Range
Dim CellValue

Set wks = Worksheets("Daily Hours VB Test")
Workers = Array("a4:a29")
Week1 = Array("b4:h4")
Week2 = Array("i4:o4")

For WorkerCounter = LBound(Workers) To UBound(Workers)
On Error Resume Next
For Wk1Counter = LBound(Week1) To UBound(Week1)
Set Rg2Add1 = wks.Range(Week1(Wk1Counter))
For Each cell In Rg2Add1.Cells
CellValue = cell.Value
If CellValue 12 Then
dt = CellValue - 12
ot = 4
st = 8
ElseIf CellValue 8 Then
ot = CellValue - 8
st = 8
ElseIf CellValue <= 8 Then
st = CellValue
End If
If AccST1 + st <= 40 Then
AccST1 = AccST1 + st
AccOT = AccOT + ot
AccDT = AccDT + dt
Else
ot = AccST1 + st - 40
AccST1 = 40
AccOT = AccOT + ot
AccDT = AccDT + dt
End If
st = 0
ot = 0
dt = 0
Next cell
Next Wk1Counter
wks.Cells(WorkerCounter + 4, 17) = AccST1
For Wk2Counter = LBound(Week2) To UBound(Week2)
Set Rg2Add2 = wks.Range(Week2(Wk2Counter))
For Each cell In Rg2Add2.Cells
CellValue = cell.Value
If CellValue 12 Then
dt = CellValue - 12
ot = 4
st = 8
ElseIf CellValue 8 Then
ot = CellValue - 8
st = 8
ElseIf CellValue <= 8 Then
st = CellValue
End If
If AccST2 + st <= 40 Then
AccST2 = AccST2 + st
AccOT = AccOT + ot
AccDT = AccDT + dt
Else
ot = AccST2 + st - 40 + ot
AccST2 = 40
AccOT = AccOT + ot
AccDT = AccDT + dt
End If
st = 0
ot = 0
dt = 0
Next cell
Next Wk2Counter
wks.Cells(WorkerCounter + 4, 18) = AccST2
wks.Cells(WorkerCounter + 4, 19) = AccST2 + AccST1
wks.Cells(WorkerCounter + 4, 20) = AccOT
wks.Cells(WorkerCounter + 4, 21) = AccDT
st = 0
ot = 0
dt = 0
AccST1 = 0
AccST2 = 0
AccOT = 0
AccDT = 0
Next WorkerCounter

End Sub
--
rpw