VBA date conversion

[Aaron]

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

 

[Per Jessen]

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

 

[Rick Rothstein]

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

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

 

[Aaron]

Thanks guys....works great!