开发者

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".

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜