Thread: VBA to loop through names and dates adding rows

Hi all,

This is my first post in this forum and I'm hoping someone may be able to help me.

I am very new to using VBA to create my macros, previously I have simply used "record".

I have attached an example .xls file which, I hope, shows what I need to do. problem.xls

For each name in the list, i need to fill in the dates between the "Date In" and "Date Out" values. I have been able to do this when there is only
one person in the list but get stuck at looping through all other names.

Any help would be enormously appreciated.

Thanks.

Here is a macro to do it:
It assumes titles row is in row 1 and start working at row 3 as it refers to an offset of -1 row to look at previous data.

Public Sub test()
Dim C_ell As Range, D_ateS As Date, D_ateE As Date
For Each C_ell In Range("A3", Cells(Rows.Count, 1).End(xlUp))
  If C_ell = C_ell.Offset(-1, 0) Then
    D_ateS = C_ell.Offset(-1, 2)
    D_ateE = C_ell.Offset(0, 2)
    While D_ateS + 1 < D_ateE
        C_ell.Offset(-1, 0).Resize(1, 1).EntireRow.Offset(1, 0).Insert
        C_ell.Offset(-1, 0).Resize(1, 5).FillDown
        C_ell.Offset(-1, 2) = D_ateS + 1
        D_ateS = D_ateS + 1
    Wend
  Else
    D_ateS = C_ell.Offset(-1, 2)
    D_ateE = C_ell.Offset(-1, 4)
    While D_ateS + 1 <= D_ateE
      C_ell.Offset(-1, 0).Resize(1, 1).EntireRow.Offset(1, 0).Insert
      C_ell.Offset(-1, 0).Resize(1, 5).FillDown
      C_ell.Offset(-1, 2) = D_ateS + 1
      D_ateS = D_ateS + 1
    Wend
  End If
Next
D_ateS = Cells(Rows.Count, 3).End(xlUp)
D_ateE = Cells(Rows.Count, 5).End(xlUp)
While D_ateS + 1 <= D_ateE
  Cells(Rows.Count, 1).End(xlUp).Resize(2, 5).FillDown
  Cells(Rows.Count, 3).End(xlUp) = D_ateS + 1
  D_ateS = D_ateS + 1
Wend
End Sub

Thanks a million p24leclerc - that's immensely helpful!