| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through records
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping through records
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping through records
Cells uses the 1st number or letter as the row and the second as the column
If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "neil" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping through records
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping through records
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete records when certain records have duplicate column data | New Users to Excel | |||
| Looping thorough records | Excel Programming | |||
| Delete two records together with looping | Excel Programming | |||
| urgent. looping thru all records and applynig formula to get new results | Excel Programming | |||
| Looping Through Records. | Excel Programming | |||
Linear Mode
