Hi, I have a table below which are timed tasks. Each task takes 20 minutes max. I need to group tasks that are at least 20 minutes apart.
I have the following VBA which works to assign the first tasks to "Person 1", but can't get it to loop past that to allocate the next unassigned task to "Person 2", "Person 3" etc. A person cannot complete more than 6 tasks in a row.
Thanks
Sub MarkCells()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim prevTime As Date
Dim i As Long
ws.Range("H2").Value = 1
prevTime = ws.Range("G2").Value
For i = 3 To 19
If IsEmpty(ws.Cells(i, "H")) Then
If ws.Cells(i, "G").Value - prevTime >= TimeSerial(0, 20, 0) Then
ws.Cells(i, "H").Value = 1
prevTime = ws.Cells(i, "G").Value
End If
End If
Next i
End Sub
I have the following VBA which works to assign the first tasks to "Person 1", but can't get it to loop past that to allocate the next unassigned task to "Person 2", "Person 3" etc. A person cannot complete more than 6 tasks in a row.
Thanks
Book1 | |||||
---|---|---|---|---|---|
G | H | I | |||
1 | G | H | Expected | ||
2 | 01/01/2024 06:00 | 1 | 1 | ||
3 | 01/01/2024 06:10 | 2 | |||
4 | 01/01/2024 06:15 | 3 | |||
5 | 01/01/2024 06:20 | 1 | 1 | ||
6 | 01/01/2024 06:25 | 4 | |||
7 | 01/01/2024 06:25 | 5 | |||
8 | 01/01/2024 06:30 | 2 | |||
9 | 01/01/2024 06:30 | 6 | |||
10 | 01/01/2024 06:40 | 1 | 1 | ||
11 | 01/01/2024 06:40 | 3 | |||
12 | 01/01/2024 06:45 | 4 | |||
13 | 01/01/2024 06:55 | 2 | |||
14 | 01/01/2024 06:55 | 5 | |||
15 | 01/01/2024 06:55 | 6 | |||
16 | 01/01/2024 06:55 | 7 | |||
17 | 01/01/2024 07:00 | 1 | 1 | ||
18 | 01/01/2024 07:00 | 3 | |||
Sheet1 |
Sub MarkCells()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim prevTime As Date
Dim i As Long
ws.Range("H2").Value = 1
prevTime = ws.Range("G2").Value
For i = 3 To 19
If IsEmpty(ws.Cells(i, "H")) Then
If ws.Cells(i, "G").Value - prevTime >= TimeSerial(0, 20, 0) Then
ws.Cells(i, "H").Value = 1
prevTime = ws.Cells(i, "G").Value
End If
End If
Next i
End Sub