Macro to Autofill Dates between Start Date and End Date

[johnzinck]

I am looking for help re: macro code for the following:

- I have numerous rows of data, each with a start date (column G) and end date (column H)
- I am trying to write a macro that will populate all dates between, including start date, across each row, beginning at column L and populating to the right
> The caveat to this is I would like the dates returned to exclude weekends (network days only) and also holidays (whereby a named range of holiday dates would be referenced)


Hoping someone can provide the correct coding and assist me with this. I have been working towards a solution for far too long on my own - I need someone else's expertise.

Many thanks!!

John

---------------
Continuous learning with Excel is a necessity as one will never know everything.

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I think this does what you have asked for.

The list of holidays needs to be stored as Excel dates on a worksheet called Holidays.
The dates are written to a worksheet called Date.
The names can easily be changed.

Sub WriteDates()

    Const stCol As Long = 12 ' Start column for output
    
    Dim iCol As Long
    Dim iRow As Long
    
    Dim StartDate As Date
    Dim EndDate As Date
    Dim ThisDate As Date
    
    Dim arr
    Dim wsHol As Worksheet: Set wsHol = ThisWorkbook.Worksheets("Holidays")
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Dates")
    
    ' Get Holidays
    arr = wsHol.UsedRange.Value2    ' value2 reads dates as serials
  
    ' Write Dates
    With ws
        
        For iRow = 2 To .Cells(.Rows.Count, "G").End(xlUp).Row
        
            StartDate = .Range("G" & iRow).Value
            EndDate = .Range("H" & iRow).Value
            ThisDate = StartDate
            iCol = 0
            Do While ThisDate < EndDate
                ThisDate = Application.WorkDay(StartDate - 1, iCol + 1, arr)
                If ThisDate <= EndDate Then .Cells(iRow, iCol + stCol).Value = ThisDate
                iCol = iCol + 1
            Loop
        Next
        .UsedRange.NumberFormat = "dd/mm/yyyy"
        
    End With
    
End Sub

 

[johnzinck]

Thank you RickXL - this is great! Works perfectly. Such a great help. Many many thanks!!

I adjusted the code to exclude the end date (in my data set, the end date is the return date so not included in my calculations).

If ThisDate < EndDate Then .Cells(iRow, iCol + stCol).Value = ThisDate

 

[RickXL]

No problem and thanks for the update.