Macro to fill dates

E

erock24

Well-known Member
Joined
Oct 26, 2006
Messages
1,163
The following macro will fill down dates in col I starting with the last date entered to the end of that month. But I have to change the number of days manually from 31 to 30, 28, etc. How can this macro be edited to always fill to the last day any given month.

LastDate = DateValue("31/" & Month(.Value) & "/" & Year(.Value)) - .Value

Code: 
Dim LastRow As Long
Dim LastDate As Long
LastRow = Cells(Rows.Count, "I").End(xlUp).Row
    With Cells(LastRow, "I")
        LastDate = DateValue("31/" & Month(.Value) & "/" & Year(.Value)) - .Value
On Error Resume Next
        .AutoFill Destination:=Range("I" & LastRow & " :I" & LastRow + LastDate), Type:=xlFillDefault
    End With

Original post was: http://www.excelforum.com/excel-programming/668430-macro-to-auto-fill-dates-in-column.html
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Change this part

LastDate = DateValue("31/" & Month(.Value) & "/" & Year(.Value)) - .Value

to be the FIRST day of NEXT month minus 1, minus .value

LastDate = (DateValue("1/" & Month(.value)+1 & "/" & Year(.Value)) - 1) - .Value

That is now the LAST day of current month (weather it be 28 29 30 or 31) - .Value

Hope that helps..
 
Upvote 0
when i change to the new LastDate code the macro errors out on the AutoFill Destination part. Saying method range of object global failed?? Any ideas.

Thanks,
 
Upvote 0
Code: 
Sub test()
Dim LastRow As Long
Dim LastDate As Long
LastRow = Cells(Rows.Count, "I").End(xlUp).Row
    With Cells(LastRow, "I")
    LastDate = (DateValue("1/" & Month(.Value) + 1 & "/" & Year(.Value)) - 1) - .Value
'On Error Resume Next
        .AutoFill Destination:=Range("I" & LastRow & " :I" & LastRow + LastDate), Type:=xlFillDefault
    End With
    End Sub
 
Upvote 0
Try it like this

Code: 
Dim LastRow As Long
Dim LastDate As Long
LastRow = Cells(Rows.Count, "I").End(xlUp).Row
    With Cells(LastRow, "I")
    LastDate = (DateSerial(Year(.Value), Month(.Value) + 1, 1) - 1) - .Value
On Error Resume Next
        .AutoFill Destination:=Range("I" & LastRow & " :I" & LastRow + LastDate)
    End With
 
Upvote 0
Is there a way to do this with a date range with a user input. For example, user inputs begin date of 10/1/2013 into cell A1 and the enddate 9/30/2014 in B1 and have this macro create sequencial dates listed in column B2 from 10/1/2013 to 9/30/2014?





Jonmo1 said:
Try it like this

Code: 
Dim LastRow As Long
Dim LastDate As Long
LastRow = Cells(Rows.Count, "I").End(xlUp).Row
    With Cells(LastRow, "I")
    LastDate = (DateSerial(Year(.Value), Month(.Value) + 1, 1) - 1) - .Value
On Error Resume Next
        .AutoFill Destination:=Range("I" & LastRow & " :I" & LastRow + LastDate)
    End With
Click to expand...
 
Upvote 0
xwavesx said:
Is there a way to do this with a date range with a user input. For example, user inputs begin date of 10/1/2013 into cell A1 and the enddate 9/30/2014 in B1 and have this macro create sequencial dates listed in column B2 from 10/1/2013 to 9/30/2014?
Click to expand...
try
Code: 
Sub filldates()
    
    [a1].Copy [b2]
    [b2].Resize([b1] - [a1] + 1).DataSeries

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Creating a Calendar for a Month with tasks
Replies
0
Views
175
anuradhagrewal
A
E
  • Locked
  • Question
Bi-Monthly cut-off payroll period
Replies
1
Views
224
Peter_SSs
Peter_SSs
S
  • Solved
Check for a specific month in a column
Replies
6
Views
229
Jeffrey Mahoney
Jeffrey Mahoney
M
  • Solved
Macro fills formula in the alternate row.
Replies
5
Views
156
motilulla
M
I
  • Solved
Set value for range being copied over with VBA
2
Replies
14
Views
320
ItalianPlatinum
I

Forum statistics

Threads
1,216,105
Messages
6,128,859
Members
449,472
Latest member
ebc9

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top