Set Cell to 1st of Next Month - VBA

[jollywood]

You all have been a wonderful help so far...thank you!

The last and final step of my workbook is to set the cells within a column to the first of the following month. So, for example, this month, the cells would say "11/1/2011"...in November, the cells would say "12/1/2011"

I have no idea where to even begin and I know it has to be easy.

I'm thinking something along these lines but this only gives the current date:

Sub DataScrubber()

Dim i As Long
Dim LastRow As Long

LastRow = Range("A65536").End(xlUp).Row
Dim cStartRange As Range
Set cStartRange = Range("Q2", Cells(LastRow, 17))

For i = 1 To LastRow - 1
    cStartRange.Cells.Value = Date
Next i
    

End Sub

 

[Robyn]

=DATE(YEAR(TODAY())+IF(MONTH(TODAY())=12,1,0),MONTH(TODAY())+IF(MONTH(TODAY())=12,-11,1),1)

The formula resolving to: =date(year,month,day)

The first part keeps the current year, unless today's month is December. In that case, 1 year must be added to the current year.

The second part adds one month to the current, unless today's month is December. In that case, 11 months must be DEDUCTED from the current month, yielding Month #1 - January.

The third part is merely the number 1. This always yields the first day of the month.

This logic goes way way way way back to early Lotus. It is possible there is now a more streamlined way. If so, I'd be interested for any other responders to post it. However, this method will always work.

 

[HalfAce]

In your code, try changing this line from:

cStartRange.Cells.[COLOR=Red]Value[/COLOR] = [COLOR=Red]Date[/COLOR]

to something like:

cStartRange.Cells.[COLOR=Red]Formula[/COLOR] = [COLOR=Red]"=EOMONTH(TODAY(),0)+1"[/COLOR]
    'Use the next line only if you want your dates to be fixed values.
    cStartRange.Value = cStartRange.Value

Hope it helps.

 

[Jonmo1]

try

=DATE(YEAR(TODAY()),MONTH(TODAY()+1),1)

 

[jollywood]

Thanks HalfAce for seeing that I wanted to use VBA AND having the correct answer. I appreciate it!!!

 

[HalfAce]

Robyn:

It is possible there is now a more streamlined way. If so, I'd be interested for any other responders to post it.

Is this what you meant?

=EOMONTH(TODAY(),0)+1

(I don't think an add-in is needed for this but I suppose I could be wrong.)

Hope it helps.

 

[HalfAce]

vba AND the correct answer???
Man, you ain't picky, are ya?

 

[Rick Rothstein]

The last and final step of my workbook is to set the cells within a column to the first of the following month. So, for example, this month, the cells would say "11/1/2011"...in November, the cells would say "12/1/2011"

Set cStartRange = Range("Q2", Cells(LastRow, 17))

First off, to make things easier for you in the future, the quoted code line above can be written like this...

Set cStartRange = Range("Q2", Cells(LastRow, "Q"))

The second argument to the Cells property call does not need to be a number... you can use the column letter, in quotes, instead.

Now, assuming you want each cell to contain the same first-of-the-month date, you can make the assignment all at once like this...

cStartRange.Cells.Value = DateSerial(Year(Date), Month(Date) + 1, 1)

This code line replaces the For..Next loop you showed us.

 

[Robyn]

HalfAce:

Is this what you meant?
Code:
=EOMONTH(TODAY(),0)+1

Yes, I suppose it is. But - it is an add on. Microsoft Help advises that if you get the #NAME? error, you must install the Analysis ToolPak add-in. Since I got this error, I have to think whether I want to get the add-in. (Depends on what other goodies it offers...) Thanks for the tip. I'll think about it.

 

[Robyn]

jonmo1:

try

=DATE(YEAR(TODAY()),MONTH(TODAY()+1),1)

I, too, missed that the original post looked for a solution in VBA, and posted a non-VBA formula. In cases where a formula is applicable, the one you suggested works in most months. Assuming the spreadsheet is to be used year round, December presents a problem that must be addressed. In December, the next month is not in today's year. The formula must adjust for that. It must also adjust for the fact that the next month is not the current month +1. That would be month #13, which doesn't exist. The formula I proposed - cumbersome as it is - makes the adjustments. The EOMONTH function suggested by HalfAce looks great. I don't have the add in it requires - but I may get it!