VBA Macro to Return Consecutive Month-ends
This is my first ever post on this website.
I need help on some VBA code that will put 31/07/00 into cell “B2” and then each month-end date down the col开发者_运维技巧umn and stop at 31/11/2010 in cell “B126” (ie. cell B2 = 31/07/00, BA3 = 31/08/00, B4 = 30/09/00 etc etc). Should I do a loop for this? I can’t really do it with the macro recorder which is how I am learning most of my VBA coding.
Thanks everyone for your assistance.
Sub FillDates()
With Sheet1.Range("B2")
.Value = DateSerial(2000, 7, 31)
.AutoFill .Resize(125, 1), xlFillMonths
End With
End Sub
This puts the first date in B2, then uses Excel's Autofill (the fill handle from Joel's answer) to fill the months down in the next 125 rows.
Why do you need a macro to do it? Excel's autofill is pretty smart.
- Put 31/7/00 into B2
- Put 31/8/00 into B3
- Select both cells
- Grab the fill handle (the tiny square in the bottom right of the selection)
- Drag it down as far as you need
Sub EndOfMonths()
Dim StartCell As Range
Dim n As Integer
'
Set StartCell = Range("B2")
StartCell.Value = DateSerial(2000, 7, 31)
'
Do
n = n + 1
StartCell.Offset(n, 0).Value = CDate(eomonth(StartCell, n))
Loop While StartCell.Offset(n, 0).Value < DateSerial(2010, 11, 30)
'
End Sub
If using Excel 2003, go to Visual Basic Editor (Alt+F11) Menu: Extras->Reference and select the "atpvbaen.xls".
精彩评论