CODEWITHSUNDEEP
excelvba
Carole
Carole

Reputation: 31

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

Upvotes: 2

Views: 1635

Answers (3)

Dick Kusleika
Dick Kusleika

Reputation: 33175

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.

Upvotes: 5

Joel Spolsky
Joel Spolsky

Reputation: 33697

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

Upvotes: 2

CaBieberach
CaBieberach

Reputation: 1768

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

Upvotes: 0

Related Questions