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 <pswa...@discussions.microsoft.com> 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
(E-Mail Removed)
"pswanie" <(E-Mail Removed)> wrote in message
news:42F42AEE-0036-4A05-AB49-(E-Mail Removed)...
>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
(E-Mail Removed)
"JW" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Sep 24, 4:58 pm, pswanie <pswa...@discussions.microsoft.com> 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" <dguille...@austin.rr.com> wrote:
> Why is the simplest approach usually the best?
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com"JW" <JWRIGH...@triad.rr.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > On Sep 24, 4:58 pm, pswanie <pswa...@discussions.microsoft.com> 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" <dguille...@austin.rr.com> wrote:
> Why is the simplest approach usually the best?
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com"JW" <JWRIGH...@triad.rr.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > On Sep 24, 4:58 pm, pswanie <pswa...@discussions.microsoft.com> 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
> (E-Mail Removed)
> "JW" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > On Sep 24, 4:58 pm, pswanie <pswa...@discussions.microsoft.com> 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 <pswa...@discussions.microsoft.com> 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
> > dguille...@austin.rr.com
> > "JW" <JWRIGH...@triad.rr.com> wrote in message
> >news:(E-Mail Removed)...
> > > On Sep 24, 4:58 pm, pswanie <pswa...@discussions.microsoft.com> 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 <pswa...@discussions.microsoft.com> 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
> > > dguille...@austin.rr.com
> > > "JW" <JWRIGH...@triad.rr.com> wrote in message
> > >news:(E-Mail Removed)...
> > > > On Sep 24, 4:58 pm, pswanie <pswa...@discussions.microsoft.com> 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