Excel VBA Create and Add multiple rows in Table from two dates

Question

I'm looking to create a UserForm1 in Excel that when a user adds in a start date and end date - it will then create new rows in a table1 that populate as follows using VBA:

Example: User enters - START DATE: 5/9/2020 & END DATE: 5/16/2020

Table Returns (Rows and calculations added to table1):

DATE          COUNT          P1/P2       START DATE      END DATE
5/9/2020        1              1          5/9/2020       5/16/2020
5/10/2020       2              2          5/9/2020       5/16/2020
5/11/2020       3              2          5/9/2020       5/16/2020
5/12/2020       4              2          5/9/2020       5/16/2020
5/13/2020       5              2          5/9/2020       5/16/2020
5/14/2020       6              2          5/9/2020       5/16/2020
5/15/2020       7              2          5/9/2020       5/16/2020

Date Column Shows: start date and a row for every day between 5/9 and 5/16 but not including 5/16

Count Column: Counts the rows added off one input

P1/P2 Column: Shows 1 for the first row added (record only) off the command and 2 for all the rest

Start Date: Shows the date entered as [start date]

End Date: Shows the date entered for [end date]

If someone enters a date that has 365 days in it, the table would end up with 365 added rows. Hoping to find a simple way I could do this like I would with SQL - but I haven't been able to find any examples in VBA.

I've been able to input data from a button on the userform which adds to the table at the end - I'm really needing direction on how to create multiple rows and manipulating the data to do what I want it to in VBA.

Appreciate any help you can offer.

Samuel Everson Samuel Everson · Accepted Answer · 2020-05-12T08:16:07

I can't write your entire program for you but this example solution will give you a working model to work from. I'll post some code and then explain what it's doing below.

Note: I am creating a new workbook, with default worksheet names and a new UserForm with all default names for the form and it's controls.

UserForm Code:

Private Sub CommandButton1_Click()
Dim StartDate As Date
Dim EndDate As Date
Dim DateRangeLength As Long
Dim LoopCounter As Long
Dim RowAfterLast As Long

StartDate = CDate(Me.TextBox1.Value)
EndDate = CDate(Me.TextBox2.Value)

DateRangeLength = (EndDate - StartDate)

With Sheet1
    For LoopCounter = 1 To DateRangeLength
        RowAfterLast = .Cells(Rows.Count, "A").End(xlUp).Row + 1
        If LoopCounter = 1 Then
            .Range("A" & RowAfterLast).Value = StartDate
            .Range("B" & RowAfterLast).Value = LoopCounter
            .Range("C" & RowAfterLast).Value = 1
            .Range("D" & RowAfterLast).Value = StartDate
            .Range("E" & RowAfterLast).Value = EndDate
        Else
            .Range("A" & RowAfterLast).Value = StartDate + (LoopCounter - 1)
            .Range("B" & RowAfterLast).Value = LoopCounter
            .Range("C" & RowAfterLast).Value = 2
            .Range("D" & RowAfterLast).Value = StartDate
            .Range("E" & RowAfterLast).Value = EndDate
        End If
    Next LoopCounter
End With

End Sub

I pass both the dates from the userform to StartDate and EndDate respectively using the CDate() function which converts it to a date value (otherwise you might experience problems with some dates).

I find the difference between the dates as an integer and assign it to DateRangeLength which is declared with the Long data type.

Then using a For...Next loop with DateRangeLength as the upper bound of the loop, we write the data to the worksheet. In my example the range is between columns "A" and "E". The row is found at the start of each loop iteration by finding the last used row in column A and adding 1 which is assigned to RowAfterLast.

Screenshots:

The output range and UserForm ready to click 'Go!' with test data:

Output after the code is run:

I should mention

You would be wise to add some kind of error checking with your user input data, such as some statements or function that makes sure the value is a valid date and in the right format.

Google is your friend if you're not too sure what to do or where to start with this.

To give you a good example of why this is important, if I enter the date values in your question in their format of MM/DD/YYYY I end up with no output because of my date settings - the correct format for my reigon is DD/MM/YYYY so the start date 05/09/2020 is understood as September 5th, 2020 and the end date is understood as 05/16/2020 but because there is no 16th month, translates to your intended May 16th, 2020 which results in DateRangeLength equalling -112 and as such the loop exits without even starting the first iteration.

Excel VBA Create and Add multiple rows in Table from two dates

1 Answers

Screenshots:

I should mention