VBA date conversion

  • Thread starter Thread starter Aaron
  • Start date Start date
A

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!!
 
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
 
cell = DateSerial(Year(cell), (Month(cell) + 1), 1) - 1

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