Sorry for the sort of "duplicate" post. Because your code does not validate
that the entry made by the user is numeric only, that is why I added a
conversion of the hrs.value to a double precision number. It may still give
an error, but you should look to see when the line OT2Hrs is being evaluated,
what cell is being referenced for Hrs.Value. When this error occurs, debug
the error and open/use the watch window. In the watch window add the
following watch:

hrs.address

And that is the cell that is probably giving you the problem (might have
text instead of a number) which would not be really a "programming" error per
se, but a data entry error combined with no entry checking prior to doing
math with some other character.

One problem that could occur is if an employee enters a negative before the
number. This would actually result in a removal of hours or like they
vanished from the office. :) These are some things you might want to assess
prior to actually doing the math on the data. :\

"neil" wrote:

Thank you, Don

I'm getting a runtime error 13, type mismatch on the line
OT2Hrs = OT2Hrs + Hrs.Value - 12

The first column in my sheet contains only the employee ID & columns B
through AF contain the attendance record. I need summary in Column AH & after.

Another question: does the "b" in your post refer to the column B?

Thanks


"Don Guillett" wrote:

Try this. You may have to adjust the columns.
Hard to tell since you did not provide sample data. with before/after
Loops for each in col B and does NOT select

Sub ComputeHrsSAS()
Dim StdHrs As Long
Dim OT1Hrs As Long
Dim OT2Hrs As Long
Dim Hrs As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 2 To Cells(Rows.Count, "b").End(xlUp).Row
For Each Hrs In Range(Cells(i, "b"), Cells(i, 30))
Select Case Hrs.Value
Case Is 12
StdHrs = StdHrs + 8
OT1Hrs = OT1Hrs + 4
OT2Hrs = OT2Hrs + Hrs.Value - 12
Case Is 8
StdHrs = StdHrs + 8
OT1Hrs = OT1Hrs + Hrs.Value - 8
StdHrs = StdHrs + Hrs.Value
Case Else
End Select

Cells(i, 32).Value = StdHrs
Cells(i, 33).Value = OT1Hrs
Cells(i, 34).Value = OT2Hrs
Next Hrs
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"neil" wrote in message
...
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