needing to increment a date by one day and print to excel 1

[VBAnovice43]

I need to increment a date (in the format of mm//dd//yy) by one and take that range of dates and put them in a column of cells in Excel. So it would appear like this:

10/1/03
10/2/03
10/3/03
10/4/03
10/5/03
10/6/03
10/7/03
10/8/03
10/9/03
10/10/03

Here's the catch: The program asks the user to input a report date...so the program will print out the 1st of the month to the date that the user specified from an input box. What would be the easiest code to do this?

 

[Zathras]

Here is one possibility:
[blue]

Option Explicit

Sub test()
  InsertDates Range("A2"), "11/16/03"
End Sub

Sub InsertDates(FirstCell As Range, LastDate As Date)
Dim nMonth As Integer
Dim nDay As Integer
Dim nYear As Integer
Dim rng As Range

  nMonth = month(LastDate)
  nDay = Day(LastDate)
  nYear = Year(LastDate)
  
  FirstCell.Value = nMonth & "/1/" & nYear
  Set rng = Range(FirstCell.Offset(1, 0), FirstCell.Offset(nDay - 1, 0))
  rng.FormulaR1C1 = "=R[-1]C+1"
  With Union(rng, FirstCell)
    .Copy
    .PasteSpecial (xlPasteValues)
  End With
  Application.CutCopyMode = False
  Set rng = Nothing
  FirstCell.Select
End Sub

[/color]
Note it does not re-set or clear any dates from a previous month. You will need to add code to handle that.