Looping through multiple rows of dates whilst interating dates inbetween

[MaxD]

Hi MrExcel troops,

My time has finally come to ask a question rather than rely on all the answers as I'm having a brain freeze moment!

I've got a dataset showing the input table on the left & the output table on the right. The first date range interates as I'd expect, however I can't get the next rows to iterate their date ranges or indeed the other cells to be populated. Step 1 is to get the dates to iterate.

Sub GenerateDates()
    Application.ScreenUpdating = False
    
    Dim i, j As Integer
    Dim finalRow, finalRow2, startRow As Long
    
    finalRow = Range("A" & Rows.Count).End(xlUp).Row
    finalRow2 = Range("M" & Rows.Count).End(xlUp).Row
    
    For i = 2 To 4 'finalRow
        finalRow2 = Range("M" & Rows.Count).End(xlUp).Row
        startRow = finalRow2 + 1
    
        Range("M" & startRow) = Range("A" & i)
        
        j = i
        
        Days = Int((Range("B" & i) - Range("A" & i)))
           
        Do While j < 2 + Days
            j = j + 1
            
            Range("M" & j) = Range("M" & j - 1) + 1
        Loop
        
        Range("N" & startRow) = Range("C" & i)
        Range("O" & startRow) = ""
        Range("P" & startRow) = Range("F" & i)
        Range("Q" & startRow) = Range("G" & i)
        
    Next i
    
End Sub

Creating the mini-sheet keeps crashing my Excel, so here's the input and output table:

What I'm trying to achieve:

 

[MaxD]

Fixed the issue with the mini-sheet creation:

Book4
	A	B	C	D	E	F	G					L	M	N	O	P	Q
1	Start date	End date	Type	Start date	End date portion	Name	Country						Date	Type	Day	Name	Country
2	12/09/2022	17/09/2022	Holiday	5		Name 1	United States
3	15/08/2022	19/08/2022	Time off in lieu	5		Name 2	United Kingdom
4	06/10/2022	11/10/2022	Holiday	4		Name 2	United Kingdom
5	15/08/2022	19/08/2022	Holiday	5		Name 3	Hungary
6	02/09/2022	02/09/2022	Holiday	1		Name 4	United States
7	25/11/2022	25/11/2022	Holiday	1		Name 5	United Kingdom
8	26/12/2022	30/12/2022	Holiday	4		Name 6	United Kingdom
9	15/08/2022	19/08/2022	Holiday	5		Name 6	United Kingdom
Sheet1

 

[MaxD]

Step 1: resolved

For i = 2 To finalRow

    startDate = Range("A" & i).Value
    endDate = Range("B" & i).Value
    startDate = startDate - 1

    Do While startDate < endDate
        finalRow2 = Range("M" & Rows.Count).End(xlUp).Row
        finalRow2 = finalRow2 + 1
        startDate = DateAdd("d", 1, startDate)
        Range("M" & finalRow2).Value = startDate
    Loop

Next i

 

[MaxD]

Full code fix below:

Sub GenerateDates()

Application.ScreenUpdating = False

Dim i, j, numDays As Integer
Dim finalRow, finalRow2 As Long
Dim startDate, endDate As Date
Dim strType, strName, strCountry As String

finalRow = Range("A" & Rows.Count).End(xlUp).Row
finalRow2 = Range("M" & Rows.Count).End(xlUp).Row

If finalRow2 > 1 Then
    Range("M2:Q" & finalRow2).Select
    Selection.ClearContents
End If

Range("A1").Select

For i = 2 To finalRow
    startDate = Range("A" & i).Value
    endDate = Range("B" & i).Value
    startDate = startDate - 1
    strType = Range("C" & i).Value
    numDays = Range("D" & i).Value
    strName = Range("F" & i).Value
    strCountry = Range("G" & i).Value

    Do While startDate < endDate
        finalRow2 = Range("M" & Rows.Count).End(xlUp).Row
        finalRow2 = finalRow2 + 1
        startDate = DateAdd("d", 1, startDate)
        Range("M" & finalRow2).Value = startDate
        Range("N" & finalRow2).Value = strType
        'Function to split decimal numDays
        Range("P" & finalRow2).Value = strName
        Range("Q" & finalRow2).Value = strCountry
    Loop
Next i

Application.ScreenUpdating = True

End Sub