From the below code iam trying to compare my active cell with previous date
, if the active cell is less than previous month then the result need to be
Yes, else No.

The problem iam facing from the below code in variable mmyy, when i run
macro this variable is shown as Text inspite of Date because of which its not
able to compare with previous date.

Example: my date column is copied from MSAccess so the date is in exact this
formate SEP-08

sub test()
PrvMth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmyy")
ActiveCell.Offset(1, 0).Select
a = ActiveCell
m = DatePart("m", a)
If (m < 10) Then
m = 0 & m
End If
y = Right(a, 2)
mmyy = m & y
MsgBox mmyy & vbLf & PrvMth
If mmyy < PrvMth Then
ActiveCell.Offset(0, 1) = "Yes"
Else
ActiveCell.Offset(-1, 0).Select
End If
End If
End If
End sub

You can get the previous month from the Date function using the Month
function...

PreviousMonth = Month(Date) - 1

And assuming the ActiveCell contains a real date, you can get its month the
same way...

MonthForActiveCell = Month(Range(ActiveCell.Value))

Since both of these will be numbers, you can compare them directly...

If MonthForActiveCell < PreviousMonth Then

--
Rick (MVP - Excel)


"Ranjit kurian" <(E-Mail Removed)> wrote in message
news:5E62248F-1451-4FB8-81AC-(E-Mail Removed)...
> From the below code iam trying to compare my active cell with previous
> date
> , if the active cell is less than previous month then the result need to
> be
> Yes, else No.
>
> The problem iam facing from the below code in variable mmyy, when i run
> macro this variable is shown as Text inspite of Date because of which its
> not
> able to compare with previous date.
>
> Example: my date column is copied from MSAccess so the date is in exact
> this
> formate SEP-08
>
> sub test()
> PrvMth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmyy")
> ActiveCell.Offset(1, 0).Select
> a = ActiveCell
> m = DatePart("m", a)
> If (m < 10) Then
> m = 0 & m
> End If
> y = Right(a, 2)
> mmyy = m & y
> MsgBox mmyy & vbLf & PrvMth
> If mmyy < PrvMth Then
> ActiveCell.Offset(0, 1) = "Yes"
> Else
> ActiveCell.Offset(-1, 0).Select
> End If
> End If
> End If
> End sub

Hi Rick,

Thanks for the reply, but the below code "MonthForActiveCell =
Month(Range(ActiveCell.Value))" was not wroking for me, so when i removed the
Range from the code "mmyy = Month(ActiveCell.Value) & Year(ActiveCell.Value)"
its working fine, but if my date is as of Dec-2008 it will show it as 122009,
pls advise ...




"Rick Rothstein" wrote:

> You can get the previous month from the Date function using the Month
> function...
>
> PreviousMonth = Month(Date) - 1
>
> And assuming the ActiveCell contains a real date, you can get its month the
> same way...
>
> MonthForActiveCell = Month(Range(ActiveCell.Value))
>
> Since both of these will be numbers, you can compare them directly...
>
> If MonthForActiveCell < PreviousMonth Then
>
> --
> Rick (MVP - Excel)
>
>
> "Ranjit kurian" <(E-Mail Removed)> wrote in message
> news:5E62248F-1451-4FB8-81AC-(E-Mail Removed)...
> > From the below code iam trying to compare my active cell with previous
> > date
> > , if the active cell is less than previous month then the result need to
> > be
> > Yes, else No.
> >
> > The problem iam facing from the below code in variable mmyy, when i run
> > macro this variable is shown as Text inspite of Date because of which its
> > not
> > able to compare with previous date.
> >
> > Example: my date column is copied from MSAccess so the date is in exact
> > this
> > formate SEP-08
> >
> > sub test()
> > PrvMth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmyy")
> > ActiveCell.Offset(1, 0).Select
> > a = ActiveCell
> > m = DatePart("m", a)
> > If (m < 10) Then
> > m = 0 & m
> > End If
> > y = Right(a, 2)
> > mmyy = m & y
> > MsgBox mmyy & vbLf & PrvMth
> > If mmyy < PrvMth Then
> > ActiveCell.Offset(0, 1) = "Yes"
> > Else
> > ActiveCell.Offset(-1, 0).Select
> > End If
> > End If
> > End If
> > End sub
>
>

Yes, the I added the Range call by mistake. I'll show you how to get the
mmyy value you want, but first I want to reiterate that you don't need that
in order to test if...

MonthForActiveCell < PreviousMonth

the If..Then test I gave you will do that test (once the erroneous Range
call is removed)...

PreviousMonth = Month(Date) - 1
MonthForActiveCell = Month(ActiveCell.Value)
If MonthForActiveCell < PreviousMonth Then
' Put "active cell date less than previous month" code here
End If

Okay, just in case you want to do it the way you started out...

mmyyy = Format(Month(ActiveCell.Value), "00") & _
Format(ActiveCell.Value, "yy")

--
Rick (MVP - Excel)


"Ranjit kurian" <(E-Mail Removed)> wrote in message
news:491FC4BB-722D-48A6-BEC4-(E-Mail Removed)...
> Hi Rick,
>
> Thanks for the reply, but the below code "MonthForActiveCell =
> Month(Range(ActiveCell.Value))" was not wroking for me, so when i removed
> the
> Range from the code "mmyy = Month(ActiveCell.Value) &
> Year(ActiveCell.Value)"
> its working fine, but if my date is as of Dec-2008 it will show it as
> 122009,
> pls advise ...
>
>
>
>
> "Rick Rothstein" wrote:
>
>> You can get the previous month from the Date function using the Month
>> function...
>>
>> PreviousMonth = Month(Date) - 1
>>
>> And assuming the ActiveCell contains a real date, you can get its month
>> the
>> same way...
>>
>> MonthForActiveCell = Month(Range(ActiveCell.Value))
>>
>> Since both of these will be numbers, you can compare them directly...
>>
>> If MonthForActiveCell < PreviousMonth Then
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "Ranjit kurian" <(E-Mail Removed)> wrote in message
>> news:5E62248F-1451-4FB8-81AC-(E-Mail Removed)...
>> > From the below code iam trying to compare my active cell with previous
>> > date
>> > , if the active cell is less than previous month then the result need
>> > to
>> > be
>> > Yes, else No.
>> >
>> > The problem iam facing from the below code in variable mmyy, when i run
>> > macro this variable is shown as Text inspite of Date because of which
>> > its
>> > not
>> > able to compare with previous date.
>> >
>> > Example: my date column is copied from MSAccess so the date is in exact
>> > this
>> > formate SEP-08
>> >
>> > sub test()
>> > PrvMth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmyy")
>> > ActiveCell.Offset(1, 0).Select
>> > a = ActiveCell
>> > m = DatePart("m", a)
>> > If (m < 10) Then
>> > m = 0 & m
>> > End If
>> > y = Right(a, 2)
>> > mmyy = m & y
>> > MsgBox mmyy & vbLf & PrvMth
>> > If mmyy < PrvMth Then
>> > ActiveCell.Offset(0, 1) = "Yes"
>> > Else
>> > ActiveCell.Offset(-1, 0).Select
>> > End If
>> > End If
>> > End If
>> > End sub
>>
>>