I am appending data to a table, cols A - I, approx. 200 rows each time. I want to add the date into col J for each row, for each append, using VBA

Question

I am appending approx 200 rows of data to a data set each day, Cols A - I. I want to add the date for each row into col J. I.e. rows 2 - 200 have 01/02/2022 in col J, rows 201 - 400 have 02/02/2022, etc. I have managed to write a macro that asks for the date and stores it in a variable. I now need the macro to add the date into the next blank cell in col J and paste it down until the last cell with data in col I.

Answer 1

The following code will do what you ask, if I have understood correctly:

Sub fill_column_with_dates(Optional start_date As Date, Optional start_cell As Range, Optional rows_for_each_date As Integer = 200, Optional day_count As Long)
     Dim row As Long
     Dim s As Worksheet
     Dim current_day As Long
    
     'if date is not supplied, use today
     If CDbl(start_date) = 0 Then start_date = Date
     
     ' if start_cell is not given, start in J1 of the current sheet
     If start_cell Is Nothing Then Set start_cell = ActiveSheet.Range("J1")
     
     ' get a reference to the sheet that holds the starting cell
     Set s = start_cell.Parent
     
     'if day_count is not given, fill the whole column
     If day_count = 0 Then
       day_count = (s.Rows.Count - start_cell.row) \ rows_for_each_date
     End If
     
     For current_day = 0 To day_count - 1
         Range(start_cell.Offset(current_day * rows_for_each_date), start_cell.Offset((current_day * rows_for_each_date) + rows_for_each_date - 1)).Value = DateAdd("d", current_day, start_date)
     Next
End Sub

Here are some example calls:

'starts in J1 on the active sheet and fills blocks of 200 rows starting with today's date
'ends when it cannot fit another block of 200
fill_column_with_dates

'starts in J1 on the active sheet and fills blocks of 200 rows starting with January 1, 2000
'ends when it cannot fit another block of 200
fill_column_with_dates "1/1/2000"

'starts in B1 of sheet "data" sheet and fills blocks of 200 rows starting with January 1, 2000
'ends when it cannot fit another block of 200
fill_column_with_dates "1/1/2000", worksheets("data").range("B1")

'starts in B1 of sheet "data" sheet and fills blocks of 100 rows starting with January 1, 2000
'ends when it cannot fit another block of 100
fill_column_with_dates "1/1/2000", worksheets("data").range("B1"),100

'starts in B1 of sheet "data" sheet and fills blocks of 100 rows starting with January 1, 2000
'ends after writing 20 block
fill_column_with_dates "1/1/2000", worksheets("data").range("B1"), 100, 20