Insert rows based on start/end values

Hello all - happy almost New Years!
Working on a little project for an associate, who is tracking completed work items for vendors in various parts of the world. I'm having some trouble getting the data to cooperate, and thought clearer minds might be of some assistance.

I've attached a data sample, with the data and schedule on sheet 1, and what I want the end result to look like on the "SampleResults" tab.

The goal is to insert a row for each user (on each day), with a "0" Instance Count, for each hour that falls within the schedule (start hour / end hour) where there are no completed work items logged.

For example, if User 1 is scheduled to work from 6 to 15, and has completed work items for 6-12 and 14-15, I need a line for hour 13 added for that day. If she has completed work items before or after scheduled hours, they should be left alone, and no lines added before or after. So in the same example, if User 1 had another work item completed at 17, we do not need to add another line for hour 16.

Hopefully the attachment will make it a little more clear.

Thanks for any help!

Hi,
I realised there was a small glitch in the program so I corrected it. It was occuring when there was a gap of more than 1 hour between the last row and the above row.

I also think I'm able to process night shift data. I programmed a function (Night_S) which is called at the beginning of the program. It modifies the day and time in Sheet1 in order to reduce the day by 1 and add 24 hours to the hours over midnight. This way the program knows it has to process this line as part of the actual user.

Give it a try and let me know how it performs.
Pierre

Option Explicit
Public User_no(10, 2) As Variant
Public User_Start As Integer
Public User_End As Integer

Public Sub Procees_Data()
Dim U_ser As String, C_ell As Range, i As Integer, M_onth As Integer, D_ay As Integer
Dim First_row As Boolean, Current_hour As Integer, Last_row As Boolean, First_time As Boolean
'store Users values in array
Read_Users 'read all users in sheet Users
Night_S 'transform night shift
'Scan whole sheet1 to insert rows where appropriate
Worksheets("Sheet1").Select
First_row = True
Last_row = False
First_time = True 'Variable only there to get through first row when hour equal start time
                  'otherwise you get an error because of the -1 offset
For Each C_ell In Range("A2", Cells(Rows.Count, 1).End(xlUp))
    'Assign value to variables
    U_ser = C_ell.Offset(0, 3)
    M_onth = C_ell.Offset(0, 4)
    D_ay = C_ell.Offset(0, 5)
    Current_hour = C_ell.Offset(0, 6) 'This is the hour of current row
    
    'check if NEXT row is a new entry (User, Month or day)
    If U_ser <> C_ell.Offset(1, 3) Or M_onth <> C_ell.Offset(1, 4) Or D_ay <> C_ell.Offset(1, 5) Then
        Last_row = True
    End If
        If First_row = True Then
            F_user (C_ell.Offset(0, 3)) 'Get start and end time for the user
        End If
        
        If First_row = True Then
            If Current_hour > User_Start And Current_hour <= User_End Then
                For i = User_Start To Current_hour - 1
                  Rows(C_ell.Row).Select
                  Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                  C_ell.Offset(-1, 3) = U_ser
                  C_ell.Offset(-1, 4) = C_ell.Offset(0, 4)
                  C_ell.Offset(-1, 5) = C_ell.Offset(0, 5)
                  C_ell.Offset(-1, 6) = i
                  C_ell.Offset(-1, 7) = 0
                Next i
                First_row = False
                GoTo Skip_prog
            ElseIf Current_hour = User_Start Then
              First_row = False
              GoTo Skip_prog
            End If
        End If
        
        If Last_row = True Then
            If (Current_hour > C_ell.Offset(-1, 6) + 1) And Current_hour <= User_End Then
              For i = C_ell.Offset(-1, 6) + 1 To Current_hour - 1
                  Rows(C_ell.Row).Select
                  Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                  C_ell.Offset(-1, 3) = U_ser
                  C_ell.Offset(-1, 4) = C_ell.Offset(0, 4)
                  C_ell.Offset(-1, 5) = C_ell.Offset(0, 5)
                  C_ell.Offset(-1, 6) = i
                  C_ell.Offset(-1, 7) = 0
              Next i
              Last_row = False
              GoTo Skip_prog
            End If
            If Current_hour < User_End Then
                For i = User_End To Current_hour + 1 Step -1
                  Rows(C_ell.Row + 1).Select
                  Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                  C_ell.Offset(1, 3) = U_ser
                  C_ell.Offset(1, 4) = C_ell.Offset(0, 4)
                  C_ell.Offset(1, 5) = C_ell.Offset(0, 5)
                  C_ell.Offset(1, 6) = i
                  C_ell.Offset(1, 7) = 0
                Next i
                Last_row = False
            ElseIf Current_hour = User_End Then
                Last_row = False
                First_row = True
            ElseIf Current_hour > User_End And User_End > C_ell.Offset(-1, 6) Then
                For i = C_ell.Offset(-1, 6) + 1 To User_End
                    Rows(C_ell.Row).Select
                    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                    C_ell.Offset(-1, 3) = U_ser
                    C_ell.Offset(-1, 4) = C_ell.Offset(0, 4)
                    C_ell.Offset(-1, 5) = C_ell.Offset(0, 5)
                    C_ell.Offset(-1, 6) = i
                    C_ell.Offset(-1, 7) = 0
                Next i
                Last_row = False
            Else
                First_row = True
                Last_row = False
            End If
        ElseIf First_time = False Then
          If (Current_hour > C_ell.Offset(-1, 6) + 1) And Current_hour <= User_End Then
            For i = C_ell.Offset(-1, 6) + 1 To Current_hour - 1
                Rows(C_ell.Row).Select
                Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                C_ell.Offset(-1, 3) = U_ser
                C_ell.Offset(-1, 4) = C_ell.Offset(0, 4)
                C_ell.Offset(-1, 5) = C_ell.Offset(0, 5)
                C_ell.Offset(-1, 6) = i
                C_ell.Offset(-1, 7) = 0
            Next i
          End If
        End If
Skip_prog: First_time = False
Next

'Change hours and date to normal
For Each C_ell In Range("G2", Cells(Rows.Count, 7).End(xlUp))
    If C_ell > 24 Then
        C_ell = C_ell - 24
        C_ell.Offset(0, -1) = C_ell.Offset(0, -1) + 1
    End If
Next

End Sub

Public Function F_user(User_to_F As String) As String
'This function is used by the Process_data Sub ONLY
'It returns the end time modified to suit data processing
Dim I_1 As Long
For I_1 = 0 To UBound(User_no())
    If User_to_F = User_no(I_1, 0) Then
        User_Start = User_no(I_1, 1)
        User_End = User_no(I_1, 2)
        If User_End < User_Start Then User_End = User_End + 24
        Exit For
    End If
Next I_1

End Function

Public Function Night_S()
Dim C_ell As Range
Worksheets("Sheet1").Select
For Each C_ell In Range("A2", Cells(Rows.Count, 1).End(xlUp))
  F_user_Night (C_ell.Offset(0, 3))
  If User_End < User_Start Then
    If C_ell.Offset(0, 6) < User_End + 3 Then
      C_ell.Offset(0, 6) = C_ell.Offset(0, 6) + 24
      C_ell.Offset(0, 5) = C_ell.Offset(0, 5) - 1
    End If
  End If
Next
End Function
Public Function Read_Users()
Dim i As Integer, C_ell As Range
Worksheets("Users").Select
i = 0
For Each C_ell In Range("A2", Cells(Rows.Count, 1).End(xlUp))
    User_no(i, 0) = C_ell
    User_no(i, 1) = C_ell.Offset(0, 1)
    User_no(i, 2) = C_ell.Offset(0, 2)
    i = i + 1
Next

End Function

Public Function F_user_Night(User_to_F As String)
'This function is used by the Night_S Sub ONLY
'It returns the end time as is in table
Dim I_1 As Long
For I_1 = 0 To UBound(User_no())
    If User_to_F = User_no(I_1, 0) Then
        User_Start = User_no(I_1, 1)
        User_End = User_no(I_1, 2)
        Exit For
    End If
Next I_1

End Function

Pierre