Get all dates between 2 dates in vba

I am providing you the VBA code for this problem, with comments to help you understand the process.

Please take time to read through and understand what is happening, so next time you run into a problem like this you have an understanding of where to start. If you have a go and get stuck, then feel free to ask for help but provide information on what you have tried along with code/formulas you have used.

Sub ExampleMacro()

' Define the variables
Dim LastRow As Long
Dim addrows
Dim FindDates
Dim CountDays
Dim adddays
Dim i As Long
Dim ir As Long

' Workout number of rows containing data to process
With Sheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
End With

' This is the row number we want to start processing, every time the For runs, it will add another 1 to this number so the next row is processed
addrows = 2

' Loop through until, LastRow has been reached
For ir = 1 To LastRow

' Define the number of days between the two dates
FindDates = Sheets("Sheet1").Range("B" & addrows).Value

' Define the number of days between the two dates
CountDays = Sheets("Sheet1").Range("C" & addrows).Value - Sheets("Sheet1").Range("B" & addrows).Value + 1

' Define the date to enter into Data column on Sheet 2, every time it loops through the date will be increased by 1
adddays = 0

' Loop through until, the last date has been reached
For i = 1 To CountDays

' Insert a new blank row on Sheet2 - Row2, for the data to be entered
Sheets("Sheet2").Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
' Put ID value of the row into Sheet2
Sheets("Sheet2").Range("A2").Value = Sheets("Sheet1").Range("A" & addrows).Value
' Put the date into Sheet2
Sheets("Sheet2").Range("B2").Value = FindDates + adddays
' Put the Code into Sheet2
Sheets("Sheet2").Range("C2").Value = Sheets("Sheet1").Range("D" & addrows).Value

' Increase the date by 1 day, ready for the re run of this loop
adddays = adddays + 1

' Go back to i and carry on until the number of CountDays has been matached.
Next I

' Now that all the dates for the first row of data has been processed, increase the row number by 1
addrows = addrows + 1

' Go back to ir and carry on until the number of rows with data has been completed
Next ir

End Sub

Browse More Popular Posts

Leave a Comment