VBA Current Month Minus 1

Hi, I wonder whether someone may be able to help me please.

I'm using the snippet of code below to, amongst cell formatting, insert the current month to a spreadsheet cell in the format 'mmm yy'.

Please Login or Register  to view this content.

What I'd like to do is chage this so the month which is inserted is the 'Current Month -'.

I've tried changing this line:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

but unfortunately this doesn't work.

I just wondered whether someone may be able to look at thsi please and let me know where I'm going wrong.

Many thanks and kind regards

Check out http://www.techonthenet.com/excel/formulas/dateadd.php

Hope that helps?

I am not sure if this what you are after

You can use

Please Login or Register  to view this content.

Instead of

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Originally Posted by Klaster

Check out http://www.techonthenet.com/excel/formulas/dateadd.php

Hope that helps?

Is this working?

.Value = Format(DateAdd(m, -1, Date), "1 mmm yy")

As JosephP showed, for the first date of the previous month I'd also use the eomonth function and then define the numberformat as "d mmm yy".

Please Login or Register  to view this content.

Hi @Klaster, thank you very much for taking the time to reply to my post and for including the link. This very interesting reading, particulalry when, like I, you're learning.

Kind Regards

Hi @AB33, thank you very much for taking the time to reply to my post and for putting the code together.

Unfortunately, although I didn't recieve an error message, the cell stills shows the current month.

Many thanks and kind regards

Hi @JosephP, thank you very much for taking the time to reply to my post and for putting the script together.

I tried your second suggestion, as this fits in with my requirements.

Unfortunately when I run this I receive the following message Run-time error 438: Object doesn't support this property or metyhod'. Debug highlights this line as the issue:

Please Login or Register  to view this content.

Many thanks and kind regards

Hi @Klaster, thank you very much for coming back to me with this.

I did try the line yo highlighted, but unfortuantely I recieve the following message: 'Run-time error 5: Invalid procedure call or argument'. Debug highlights this line as the cause

Please Login or Register  to view this content.

Many thanks and kind regards

Hi @berlan, thank you very much for taking the time to reply to my post and for putting the solution together, which, with a very small adjustment to the date format works great.

All the best and kind regards

Why not try dateserial?

Please Login or Register  to view this content.

Hi @JosephP, my apologies for not beiong clear. I'm using 2013 at home, but 2003 at work.

Once again my apologies for the confusion.

Kind regards