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

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.

"pswanie" wrote:

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

On Sep 24, 4:58 pm, pswanie wrote:
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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pswanie" wrote in message
...
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

Why is the simplest approach usually the best?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JW" wrote in message
ups.com...
On Sep 24, 4:58 pm, pswanie wrote:
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

On Sep 24, 6:06 pm, "Don Guillett" wrote:
Why is the simplest approach usually the best?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"JW" wrote in message

ups.com...

On Sep 24, 4:58 pm, pswanie wrote:
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

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

On Sep 24, 6:06 pm, "Don Guillett" wrote:
Why is the simplest approach usually the best?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"JW" wrote in message

ups.com...

On Sep 24, 4:58 pm, pswanie wrote:
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

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

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



"Don Guillett" wrote:

Why is the simplest approach usually the best?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JW" wrote in message
ups.com...
On Sep 24, 4:58 pm, pswanie wrote:
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

On Sep 25, 1:36 am, pswanie wrote:
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

"Don Guillett" wrote:
Why is the simplest approach usually the best?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JW" wrote in message
oups.com...
On Sep 24, 4:58 pm, pswanie wrote:
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

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

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

JW wrote:
On Sep 25, 1:36 am, pswanie wrote:
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

"Don Guillett" wrote:
Why is the simplest approach usually the best?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JW" wrote in message
oups.com...
On Sep 24, 4:58 pm, pswanie wrote:
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

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

On Mon, 24 Sep 2007 13:58:00 -0700, pswanie
wrote:

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

On Wed, 26 Sep 2007 14:54:42 -0400, Ron Rosenfeld
wrote:

On Mon, 24 Sep 2007 13:58:00 -0700, pswanie
wrote:

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