Date Autofill from Input Box

[DAyotte]

Hello All (again)
So, the macro I'm working on is working great, except for the date autofill.

The macro has an inputbox that requests the date of report from the user. This is manually entered into a textbox. Instead of filling the same date to the last used row, it goes up in incriments of years. IE: (A1) 04/01/2013, (A2) 04/01/2014, (A3) 04/01/2015, etc.

I was thinking a Copy/Paste to the last row would work, but I can't figure out how to do that. ("DateOK" is the name of my OK button, and "ReportDate" is the name of the textbox.)

Thanks again!

Here's my code:

 Private Sub DateOK_Click()  Range("A2").Value = ReportDate
  Range("A2").AutoFill Destination:=Range("A2:A" & Range("B" & Rows.Count) _
    .End(xlUp).Row), Type:=xlFillSeries
      Application.CutCopyMode = False
    ReportDate = Empty
  DateForm.Hide
End Sub

 

[Norie]

The code seems to work fine when I try it.

The values aren't actually dates as you've just put the text from ReportDate in A2.

If you actually want dates first you'll need to convert the value in the textbox to a date using DateValue or CDate.

Also, to increment by years, you can use DataSeries.

Private Sub DateOK_Click()

    Range("A2").Value = DateValue(ReportDate)
    Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row).DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
                                                                     xlYear, Step:=1, Trend:=False
    Application.CutCopyMode = False
    ReportDate = Empty

End Sub

 

[dermie_72]

try removing Type:=xlFillSeries
Also...see if filldown works better than autofill.

 

[DAyotte]

My fault for not specifying - The fact that it's change the years as it goes down is the issue. I want it to autofill the same date to the last used row. I removed the xlFillSeries, and there was no change. I also tried changing the .AutoFill (method?) to .FillDown, but it wasn't recognized.

 

[Norie]

Did you try the code I posted?

 

[DAyotte]

I did. It does the same thing as my original code.

 

[Norie]

What exactly do you want to do?

If you just want the same date in all the cells get rid of all the AutoFill DataSeries stuff and use this.

    Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row).Value = ReportDate

 

[DAyotte]

Worked like a charm. Thank you.