check for last day of month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pswanie - 24 Sep 2007 21:58 GMT

i need a mocro to check if its the last day of the month and if so del
contents in cell a1

for example

chek if day = 31 then del range a1. if only 30 days in month then
chek if day = 30 then del range a1. if only 28 days in month then
chek if day = 28 then del range a1. if non of above then nothing

it can use the system date to chek what month is the current month

Reply to this Message

PJFry - 24 Sep 2007 22:40 GMT

Try this:

Sub EndMonth()

If Range("D1") = Evaluate("=EOMONTH(Now(),0)") Then
Range("A1").Delete
End If

End Sub

You need to replace the D1 with the location of your date. Set this to run
with the workbook opens.

One note: The EOMonth function requires the Analysis ToolPack. If you get
an error, check under Tools => Add-Ins to make sure it is installed.

> i need a mocro to check if its the last day of the month and if so del
> contents in cell a1
[quoted text clipped - 6 lines]
>
> it can use the system date to chek what month is the current month

Reply to this Message

JW - 24 Sep 2007 22:42 GMT

> i need a mocro to check if its the last day of the month and if so del
> contents in cell a1
[quoted text clipped - 6 lines]
>
> it can use the system date to chek what month is the current month

Or, you can simply check if the Date + 1 equals the first of the next
month.
If Day(Date + 1) = 1 Then Range("A1").ClearContents

Reply to this Message

Don Guillett - 24 Sep 2007 23:06 GMT

Why is the simplest approach usually the best?

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>> i need a mocro to check if its the last day of the month and if so del
>> contents in cell a1
[quoted text clipped - 10 lines]
> month.
> If Day(Date + 1) = 1 Then Range("A1").ClearContents

Reply to this Message

JW - 25 Sep 2007 00:16 GMT

> Why is the simplest approach usually the best?
>
[quoted text clipped - 17 lines]
> > month.
> > If Day(Date + 1) = 1 Then Range("A1").ClearContents

Unfortunately, from my experiences, it usually isn't. Just thought
that it would be the most efficient method in this case. Agree?

Reply to this Message

JW - 25 Sep 2007 00:16 GMT

> Why is the simplest approach usually the best?
>
[quoted text clipped - 17 lines]
> > month.
> > If Day(Date + 1) = 1 Then Range("A1").ClearContents

Unfortunately, from my experiences, it usually isn't. Just thought
that it would be the most efficient method in this case. Agree?

Reply to this Message

pswanie - 25 Sep 2007 06:36 GMT

Nope sory guys..... nether of those two options worked.

i placed the date in range N31 and the range that needs to be cleared will
be c24

and i need this macro to run with in a nother one

> Why is the simplest approach usually the best?
>
[quoted text clipped - 12 lines]
> > month.
> > If Day(Date + 1) = 1 Then Range("A1").ClearContents

Reply to this Message

JW - 25 Sep 2007 12:11 GMT

> Nope sory guys..... nether of those two options worked.
>
[quoted text clipped - 24 lines]
> > > month.
> > > If Day(Date + 1) = 1 Then Range("A1").ClearContents

Should work no problem. How about this. I separated the sub out.
Now you can call it whenever and wherever you want.
Sub dateCheck(ByVal dRange As Range, ByVal clearRange As Range)
If Day(dRange + 1) = 1 Then clearRange.ClearContents
End Sub

Call it like this wherever needed and simply pass whatever arguments
you want to:
Sub testThis()
dateCheck Range("N31"), Range("C24")
End Sub

Reply to this Message

JW - 25 Sep 2007 13:06 GMT

Might want to throw a check in there too to make sure that dRange is
infact a date.
Sub dateCheck(ByVal dRange As Range, ByVal clearRange As Range)
If Not IsDate(dRange.Value) Then
MsgBox dRange.Address & " is not in date format", , "Error"
Else
If Day(dRange + 1) = 1 Then clearRange.ClearContents
End If
End Sub

> > Nope sory guys..... nether of those two options worked.
> >
[quoted text clipped - 36 lines]
> dateCheck Range("N31"), Range("C24")
> End Sub

Reply to this Message

Don Guillett - 24 Sep 2007 22:46 GMT

One way
Sub lastday()
If Day(Date) = Day(DateSerial(Year(Date), _
Month(Date), 1) - 1) Then Range("a1").ClearContents
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>i need a mocro to check if its the last day of the month and if so del
> contents in cell a1
[quoted text clipped - 6 lines]
>
> it can use the system date to chek what month is the current month

Reply to this Message

Ron Rosenfeld - 26 Sep 2007 19:54 GMT

>i need a mocro to check if its the last day of the month and if so del
>contents in cell a1
[quoted text clipped - 6 lines]
>
>it can use the system date to chek what month is the current month

Sub ClearAtEOM()
If Day(Date + 1) < Day(Date) Then [a1].ClearContents
End Sub

--ron

Reply to this Message

Ron Rosenfeld - 26 Sep 2007 20:20 GMT

>>i need a mocro to check if its the last day of the month and if so del
>>contents in cell a1
[quoted text clipped - 12 lines]
>
>--ron

I see JW has posted an even simpler method:

If Day(Date + 1) = 1 Then [a1].ClearContents

--ron

Reply to this Message