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!

set rng = cells(rows.count,1).End(xlup)(2)
rng.Resize(1,10).filldown

--
Regards,
Tom Ogilvy


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

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!

A possible alternative that takes advantage of Excel date behavior:

Sub AddMonthName()
Dim rng as Range, dt as Date
Dim s as String
set rng = cells(rows.count,1).End(xlup)
dt = DateValue(rng.Text & " 1, 2000")
if len(rng) = 3 then
s = "mmm"
else
s = "mmmm"
end if
rng.offset(1,0).Value = format(DateSerial(2000,month(dt)+1,1),s)
End sub

--
Regards,
Tom Ogilvy



"Kevin B" wrote:

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!

Hey, I really like the approach. Succint and tidy too. Thanks Tom, I've
learned a great deal by reading your post's to readers questions.
--
Kevin Backmann


"Tom Ogilvy" wrote:

A possible alternative that takes advantage of Excel date behavior:

Sub AddMonthName()
Dim rng as Range, dt as Date
Dim s as String
set rng = cells(rows.count,1).End(xlup)
dt = DateValue(rng.Text & " 1, 2000")
if len(rng) = 3 then
s = "mmm"
else
s = "mmmm"
end if
rng.offset(1,0).Value = format(DateSerial(2000,month(dt)+1,1),s)
End sub

--
Regards,
Tom Ogilvy



"Kevin B" wrote:

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!

Tom,

This code works to get the next row, however it is skipping by blank cells
and going to the end of the column where there are some aggregate totals, and
adding a new cell after that. I need something that just goes to the last
cell of the contiguous data and autofills one more.

The month/year are already formatted into the spreadsheet, so I do no
believe I need any special coding with regard to format there. I just need
the simple action of autofilling the next cell in the Column A.

Thanks to you and Kevin for your responses thus far!

Bob

"Tom Ogilvy" wrote:

set rng = cells(rows.count,1).End(xlup)(2)
rng.Resize(1,10).filldown

--
Regards,
Tom Ogilvy


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

if they are contiguous from A1

cells(1,1).End(xldown)(2).Filldown

--
Regards,
Tom Ogilvy


"Bob F" wrote:

Tom,

This code works to get the next row, however it is skipping by blank cells
and going to the end of the column where there are some aggregate totals, and
adding a new cell after that. I need something that just goes to the last
cell of the contiguous data and autofills one more.

The month/year are already formatted into the spreadsheet, so I do no
believe I need any special coding with regard to format there. I just need
the simple action of autofilling the next cell in the Column A.

Thanks to you and Kevin for your responses thus far!

Bob

"Tom Ogilvy" wrote:

set rng = cells(rows.count,1).End(xlup)(2)
rng.Resize(1,10).filldown

--
Regards,
Tom Ogilvy


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