VBA date conversion
 

I need help creating a script that will take a few ranges of cells
containing dates (A1:A30,C1:C30,E1:E30)
and convert the day in those cells to the last day of the month
(3/12/2010 becomes 3/31/2010)

Any help is greatly appreciated!!

VBA date conversion
 

Try this:

Sub EndMonthConversion()
Dim rng As Range
Dim cell As Range

Set rng = Range("A1:A30, C1:C30, E1:E30")

For Each cell In rng
cell = DateSerial(Year(cell), (Month(cell) + 1), 1) - 1
Next
End Sub

Regards,
Per


On 20 Nov., 20:39, Aaron wrote:
I need help creating a script that will take a few ranges of cells
containing dates (A1:A30,C1:C30,E1:E30)
and convert the day in those cells to the last day of the month
(3/12/2010 becomes 3/31/2010)

Any help is greatly appreciated!!

VBA date conversion
 

cell = DateSerial(Year(cell), (Month(cell) + 1), 1) - 1

Or more simply... cell = DateSerial(Year(cell), Month(cell) + 1, 0)

--
Rick Rothstein (MVP - Excel)


"Per Jessen" wrote in message
...
Try this:

Sub EndMonthConversion()
Dim rng As Range
Dim cell As Range

Set rng = Range("A1:A30, C1:C30, E1:E30")

For Each cell In rng
cell = DateSerial(Year(cell), (Month(cell) + 1), 1) - 1
Next
End Sub

Regards,
Per


On 20 Nov., 20:39, Aaron wrote:
I need help creating a script that will take a few ranges of cells
containing dates (A1:A30,C1:C30,E1:E30)
and convert the day in those cells to the last day of the month
(3/12/2010 becomes 3/31/2010)

Any help is greatly appreciated!!

VBA date conversion
 

Thanks guys....works great!