Iam working in VBA.

Iam getting an error(object doesn't support) for the previous month Code.

Iam using an excel VBA, in excel i have a column called as MyDate which
contains only months like Jan/09, Dec/08, Nov/08,Oct/08 etc... i have
changed the format manually to mmm/yy format, but when i go to each cell its
still in the format of m/d/yyyy, because of which the vba code is giving an
error to me, as my date are not fixed, i need to change the formate of excel
into mmm/yy

Iam using the variables to an IF funtions, if the CurrMth is Jan/09 then the
result should be Yes, if the PrevMth is Dec-08 the result should be AYes


"OssieMac" wrote:

Some more answers that might be closer to what you want.

On worksheet
Current Month and Year

=TEXT(TODAY(),"mmm/yy")

Previous Month and Year

=TEXT(EDATE(TODAY(),-1),"mmm/yy")

in VBA

CurrMth = Format(Date, "mmm/yy")
PrevMth = Format(WorksheetFunction.EDate(Date, -1), "mmm/yy")

Note again that WorksheetFunction.EDate does not work in VBA in some earlier
versions of XL.

--
Regards,

OssieMac


"OssieMac" wrote:

I am not sure if you want to work in VBA code or in worksheet formulas
because TODAY() is a worksheet formula and your CurrMth and PrevMth equals is
VBA.

In worksheet formulas the following returns the values you want.

Current month and year
= MONTH(TODAY()) & " " & YEAR(TODAY())

Previous month and year
=MONTH(EDATE(TODAY(),-1)) & " " & YEAR(EDATE(TODAY(),-1))

In VBA. [Note that Date replaces TODAY()]

CurrMth = Month(Date) & " " & Year(Date)

PrevMth = Month(WorksheetFunction.EDate(Date, -1)) _
& " " & Year(WorksheetFunction.EDate(Date, -1))

However, WorksheetFunction.EDate does not work in VBA in XL2002. Not sure
about xl2003 but it does work in xl2007.

--
Regards,

OssieMac


"Ranjit kurian" wrote:

I would like to give a variable for my date column, can some one advise me

Example:
I have column in this formate mmm/yy, my below variable should identify the
Month & Year, i need to variables one is for current month(Jan/09), and the
other for Previous Month(Dec/08)

CurrMth = Month(Today()) & Year(Today())
PrvMth = Month(Today()) - 1 & Year(Today())