Autofilling Macro
Assuming that you're using the month as a word and the first month is in
cell A1 of Sheet1, this should do the trick:
Sub NextMonth()
Dim wb As Workbook
Dim ws As Worksheet
Dim strMonth As String
Dim strNextMonth As String
Dim lngOffset As Long
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")
ws.Activate
Range("A1").Select
strMonth = ActiveCell.Text
Do Until strMonth = ""
lngOffset = lngOffset + 1
strMonth = ActiveCell.Offset(lngOffset).Text
Loop
strMonth = ActiveCell.Offset(lngOffset - 1).Text
Select Case strMonth
Case "January"
strNextMonth = "February"
Case "February"
strNextMonth = "March"
Case "March"
strNextMonth = "April"
Case "April"
strNextMonth = "May"
Case "May"
strNextMonth = "June"
Case "June"
strNextMonth = "July"
Case "July"
strNextMonth = "August"
Case "August"
strNextMonth = "September"
Case "September"
strNextMonth = "October"
Case "October"
strNextMonth = "November"
Case "November"
strNextMonth = "December"
Case "December"
strNextMonth = "January"
Case Else
End Select
ActiveCell.Offset(lngOffset).Value = strNextMonth
Set wb = Nothing
Set ws = Nothing
Exit Sub
End Sub
--
Kevin Backmann
"Bob F" wrote:
I need some help with some programming inside a macro.
I am trying to simply create a macro which when run, autofills the next
month (one cell at a time). I have a spreadsheet where A8 begins the listing
of months. All I need is to make a macro that finds the last filled in month
in column A and autofills the next cell (which is blank).
My problem is that I am not familiar enough with using the code to create a
macro that is dynamic in this way. I believe this is rather basic for most
who are familar with visual basic, but I just can't find anyone around the
office who can help me.
Thanks!
|