Hi All,
Just had to re-register after 8 years away. I receive a weekly timesheet spreadsheet for a construction project in an area with high rainfall. Each person can charge to 3 or 4 different time codes per day. One of the codes is "inclement weather/rain". When this code appears I need to "find" the other codes that person charged to on the same day and manually determine which "work code" was impacted by the rain. What I need in code or advanced filters is an ability to select the other "work code" that has the highest "value". So in the example below an employee charges 2 hours to rain, 0.5 hours to another work code and 9 hours to another work code, I need to select the code with 9 hours and say that this was the work impacted by rain. I've written vba that finds the rain code and copies the cell below and above but I'd like it to find the cell with the same employee name, same date and largest hours and just copy that row to another sheet - any suggestions would be great - here's the rough vba I have
For i = 1 To LastRow
With Worksheets("EMPLOYEE HOURS at 2022_02_06")
'This is working then altered to pick up CELL BELOW AND ABOVE and Copy
'because 1 of those is the cell with the work that is impacted
If .Cells(i, 10).Value = "Inclement Weather" Then
.Rows(i).Copy Destination:=Worksheets("TEST").Range("A" & j)
'.Rows(i + 1).Copy Destination:=Worksheets("TEST").Range("A" & j) 'copy row below
'.Rows(i - 1).Copy Destination:=Worksheets("TEST").Range("A" & j) 'copy row above
j = j + 1
End If
End With
Next i
Just had to re-register after 8 years away. I receive a weekly timesheet spreadsheet for a construction project in an area with high rainfall. Each person can charge to 3 or 4 different time codes per day. One of the codes is "inclement weather/rain". When this code appears I need to "find" the other codes that person charged to on the same day and manually determine which "work code" was impacted by the rain. What I need in code or advanced filters is an ability to select the other "work code" that has the highest "value". So in the example below an employee charges 2 hours to rain, 0.5 hours to another work code and 9 hours to another work code, I need to select the code with 9 hours and say that this was the work impacted by rain. I've written vba that finds the rain code and copies the cell below and above but I'd like it to find the cell with the same employee name, same date and largest hours and just copy that row to another sheet - any suggestions would be great - here's the rough vba I have
For i = 1 To LastRow
With Worksheets("EMPLOYEE HOURS at 2022_02_06")
'This is working then altered to pick up CELL BELOW AND ABOVE and Copy
'because 1 of those is the cell with the work that is impacted
If .Cells(i, 10).Value = "Inclement Weather" Then
.Rows(i).Copy Destination:=Worksheets("TEST").Range("A" & j)
'.Rows(i + 1).Copy Destination:=Worksheets("TEST").Range("A" & j) 'copy row below
'.Rows(i - 1).Copy Destination:=Worksheets("TEST").Range("A" & j) 'copy row above
j = j + 1
End If
End With
Next i
| Hours | Date | Day Rule Code | FirstName | Last Name | Full Name | Claim Class Name | WorkOrderCode | WorkOrderName |
| 2 | 31/01/2022 | ORD | Charlie | Canham | Charlie Canham | Crane Driver / Operator | ZJ20002.0002 | Mech Inclement Weather |
| 0.5 | 31/01/2022 | ORD | Charlie | Canham | Charlie Canham | Crane Driver / Operator | ZJ20002.0014 | Primary Cable ladder Install RL42.5 |
| 9 | 31/01/2022 | ORD | Charlie | Canham | Charlie Canham | Crane Driver / Operator | P5070.S.L1.1.P1.M | P5070.S.L1.1 - Pancake 1 |
