check for last day of month

[pswanie]

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

 

[PJFry]

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.

 

[JW]

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

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

 

[Don Guillett]

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

 

[Don Guillett]

Why is the simplest approach usually the best?

 

[JW]

Why is the simplest approach usually the best?

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

 

[JW]

Why is the simplest approach usually the best?

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

 

[pswanie]

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

 

[JW]

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

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

 

[JW]

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

 

[Ron Rosenfeld]

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

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


--ron

 

[Ron Rosenfeld]

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

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


--ron

I see JW has posted an even simpler method:

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


--ron