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

 

[Jonmo1]

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!

Actually, I had a typo in my post

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

And Yes, it works in December....Try it
it's smart enough to know that 12+1 = January NEXT year.

Excel Workbook
	A	B
1	12/15/2011	01/01/2012
Sheet1

 

[jollywood]

Rick, you're awesome. Taking out that loop saves a LOT of time and memory. Thanks again to everyone for helping me!

 

[Robyn]

Indeed, it does work! I'm actually stymied. I can understand that Microsoft has taught it to recognize that Month 12 + 1 = 1, not 13. What surprises me is that it got the year right even though the year argument did not specify the adjustment. Long ago, I heard a phrase something like, "I know you think you understood what I said, but I'm not sure you understood that what I said is not what I meant." Seems like it did understand. Thank you for this information. I would have never imagined it.

 

[Rick Rothstein]

I can understand that Microsoft has taught it to recognize that Month 12 + 1 = 1, not 13.

That is not what Microsoft taught it... 12 + 1 is 13 but since 13 is one month more than a year, the year is added to the year you specify for the first argument and the next month is specified. Don't think of DateSerial as specifying a date by inputting the date parts; rather, think of it as the DateAdd function on steriods (yes, VB has a DateAdd function in case you were not aware of it) The DateSerial function is simply performing date addition... the date starts at the 0th day of the 0th month for the year specified and then you tell it how many month and days to add to it... that is all that is going on here. Try this and see if it clarifies things for you...

MsgBox DateSerial(1900, 1200, 4327)

Here I have first added 1200 month to the 0th day of the 0th month of the year 1900 and then added 4327 days to the date that produced (the order of operation is important to note because of the varying number of days in the months)... doing this produces today's date (October 5, 2011 for future readers of this thread). Now, let's start at January 1, 2000 and add 129 months to that date and then add 4 days to the first day of the month/year that calculation produced...

MsgBox DateSerial(2000, 1 + 129, 1 + 4)

And again, we arrive at today's date (October 5, 2011). This latter example should more intuitively show the "DateAdd function on steroids" nature of the DateSerial function than the first example because it starts from a known date.