I found a VBA code online which does the hardest part of splitting absences data from ranges to separate line for each day. But one thing I cannot figure out how to do it is how to assign a number to each day that was requested. Could anyone help me? For better understanding see screenshot. Greatly appreciated!

Yellow and Green coloured lines separates events. Orange is the thing I am trying to accomplish. Absences

Sub One_Day_Per_Row()
  Dim a, b
  Dim rws As Long, sr As Long, i As Long, j As Long, k As Long, r As Long
  
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 6).Value
  rws = UBound(a, 1)
  For r = 1 To rws
    a(r, 6) = a(r, 5) - a(r, 4)   1
    k = k   a(r, 6)
  Next r
  If k < Rows.Count Then
    ReDim b(1 To k, 1 To 4)
    sr = 1
    For r = 1 To rws
      For i = 0 To a(r, 6) - 1
        For j = 1 To 3
          b(sr   i, j) = a(r, j)
        Next j
        b(sr   i, 4) = a(r, 4)   i
      Next i
      sr = sr   a(r, 6)
    Next r
    Range("G2").Resize(k, 4).Value = b
    Range("G1:J1").Value = Array("emp number", "emp name", "absence code", "date")
  Else
    MsgBox "Too many rows"
  End If
End Sub

CodePudding user response：

Something like this should work:

Sub Tester()

    Dim data, rw As Long, ws As Worksheet, dStart, dEnd, d, n As Long
    Dim cOut As Range
    
    Set ws = ActiveSheet 'or whatever
    
    'read the data
    data = ws.Range("A2:E" & ws.Cells(Rows.Count, "A").End(xlUp).Row).Value
    Set cOut = ws.Range("G2") 'cell to begin output
    
    For rw = 1 To UBound(data, 1)
        dStart = data(rw, 4)    'start date
        dEnd = data(rw, 5)      'end date
        n = 1                   'reset counter
        For d = dStart To dEnd  'loop date range
            cOut.Resize(1, 3).Value = Array(data(rw, 1), data(rw, 2), data(rw, 3))
            cOut.Offset(0, 3).Value = d
            cOut.Offset(0, 4).Value = n
            n = n   1
            Set cOut = cOut.Offset(1, 0) 'next output row
        Next d
    Next rw
        
End Sub