Get all dates between 2 dates in vba

by

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

More Related Contents:

  1. to make this code simpler
  2. VBA code takes long time to execute in excel
  3. How can I insert variable into formula in VBA
  4. Wait until ActiveWorkbook.RefreshAll finishes – VBA
  5. Code in VBA loops and never ends. How to fix this?
  6. Excel column number from column name
  7. Pop up the Excel Statusbar?
  8. VBA – destroy a modeless UserForm instance properly
  9. How To Speed Up VBA Code
  10. Execute a command in command prompt using excel VBA
  11. “Unable to get the VLookup property of the WorksheetFunction Class” error [duplicate]
  12. Copy data from another Workbook through VBA
  13. Excel VBA Load worksheet in Function
  14. Excel tab sheet names vs. Visual Basic sheet names
  15. Searching an excel with two search words (multiple lookup) using vba macro [closed]
  16. Excel VBA – Combine rows with duplicate values in one cell and merge values in other cell
  17. Load csv file into a VBA array rather than Excel Sheet
  18. Insert line break in wrapped cell via code
  19. Change color of certain characters in a cell
  20. Sheet protection: UserInterFaceOnly gone
  21. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  22. VBA Macro crashes after 32000 rows
  23. How to create dynamic variable names VBA
  24. How to break a long string into multiple lines
  25. Read VBA macros (or vbaProject.bin) of an Excel file without opening it in MS Excel [closed]
  26. How to get address of cell which calls a VBA Functions in a Excel sheet
  27. simple vba code gives me run time error 91 object variable or with block not set
  28. How to have vba execute every 10 minutes?
  29. Read and write from/to registry in VBA
  30. Unable to read data from a CSV using ADO due to the driver thinking I am working with integers/number and showing nulls instead of text

Leave a Comment