Haven't taken the time to id what was wrong with Don Guillet's code, but am
suggesting the following using your existing code. I didn't change it, but I
might recommend using double instead of long. That is if there is even a
remote possibility that someone could put in a part of an hour. (Long is a
long integer, which can only be a whole number so taking a half hour off may
result in a full hour being applied. :\)

Sub ComputeHrs()
Dim StdHrs As Long
Dim OT1Hrs As Long
Dim OT2Hrs As Long
Dim Hrs As Range
Dim Location As Range
Set Location = Range("B2")
Location.Select
'Selects the 31 day attendance record for the first employee
While Selection < ""
Range(Location.Address, Range(Location.Address).Offset(0, 30)).Select

For Each Hrs In Selection

Select Case CDbl(Hrs.Value) 12
Case True
StdHrs = StdHrs + 8
OT1Hrs = OT1Hrs + 4
OT2Hrs = OT2Hrs + CDbl(Hrs.Value) - 12
Case False
Select Case CDbl(Hrs.Value) 8
Case True
StdHrs = StdHrs + 8
OT1Hrs = OT1Hrs + CDbl(Hrs.Value) - 8
Case False
StdHrs = StdHrs + CDbl(Hrs.Value)
End Select
End Select

With Cells(Selection.Row, Selection.Column).Select
Selection.Offset(0, 31).Value = StdHrs
Selection.Offset(0, 32).Value = OT1Hrs
Selection.Offset(0, 33).Value = OT2Hrs
End With


Next Hrs
Set Location = Location.Offset(1, 0)
Location.Select
Wend
End Sub



"neil" wrote:

Hi,

I have a list of employees with the hours they have logged during the month.

I have to split the hours they have logged into Std & OT hours (any hours
over 8 are OT) for each employee for each day. The summary of Std Hours & OT
hours for each employee is in two columns towards the right of the list.

The layout of the table is
EmpID Day1 Day 2 Day3.... Day31 StdHrs OTHrs



How do I get VBA to do the calculation for me? While I have written the code
to get the calculation for the first employee, I don't know know how to move
to the next record for the employee & work the same calculation.


Sub ComputeHrs()
Dim StdHrs As Long
Dim OT1Hrs As Long
Dim OT2Hrs As Long
Dim Hrs As Range


'Selects the 31 day attendance record for the first employee
Range("B2", Range("B2").Offset(0, 30)).Select

For Each Hrs In Selection

Select Case Hrs.Value 12
Case True
StdHrs = StdHrs + 8
OT1Hrs = OT1Hrs + 4
OT2Hrs = OT2Hrs + Hrs.Value - 12
Case False
Select Case Hrs.Value 8
Case True
StdHrs = StdHrs + 8
OT1Hrs = OT1Hrs + Hrs.Value - 8
Case False
StdHrs = StdHrs + Hrs.Value
End Select
End Select

With Range("B2").Select
Selection.Offset(0, 31).Value = StdHrs
Selection.Offset(0, 32).Value = OT1Hrs
Selection.Offset(0, 33).Value = OT2Hrs
End With
Next Hrs

End Sub