auto insert of last day of last month

D

dolphinv4

Hi,

I'd like to create a button such that on clicking it,
cell A1 will show the last day of last month, for
example, today is 20/5/04 so on clicking the button,
it'll show 30/4/04 in cell A1.

I tried using the following macro which I had used
previously for other purposes but it doesn't seem to work
in this case:

With Range("A1")
.Value = DateSerial(Year(.Value), Month(.Value) +
2, 0)
End With

What code shld I use?

Thanks!
val
 
N

Norman Harker

Hi Dolphinv4!

You could use the cell formula:

=DATE(YEAR(TODAY()),MONTH(TODAY()),0)

Or use the subroutine:

Sub LastDayLastMonth()
ActiveSheet.Range("A1").Value = DateSerial(Year(Date), Month(Date), 0)
End Sub
 
F

Frank Kabel

Hi
in addition to Norman's answer you could also use the
EOMONTH
function. But this requires the Analysis Toolpak Addin
 
Top