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'.

With Range("B3")
                .Value = Format(Date - 1, "1 mmm yy")
                .NumberFormat = "mmm yy"
                .HorizontalAlignment = xlCenter
                .Interior.ColorIndex = 37
                    With .Font
                        .Name = "Lucida Sans"
                        .Bold = True
                        .Size = 10
                    End With
            End With

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:

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

to

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

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?

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")

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

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

Many thanks and kind regards

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

I am not sure if this what you are after

You can use

  .Value = Date - 1

Instead of

 .Value = Now - 1

Sub test()

With Range("B3")

                .Value = Now - 1
                .NumberFormat = " dd-mmm-yy"
                .HorizontalAlignment = xlCenter
                .Interior.ColorIndex = 37
                    With .Font
                        .Name = "Lucida Sans"
                        .Bold = True
                        .Size = 10
                    End With
            End With
End Sub

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:

.Value = WorksheetFunction.eomonth(Date, -2) + 1

Many thanks and kind regards

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".

With Range("B3")
    .Value = Evaluate("EoMonth(Today(), -2) + 1")
    .NumberFormat = "d mmm yy"
    .HorizontalAlignment = xlCenter
    .Interior.ColorIndex = 37
    With .Font
        .Name = "Lucida Sans"
        .Bold = True
        .Size = 10
    End With
End With

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?

            With Range("B3")
                .Value = DateSerial(Year(Now), Month(Now) - 1, 1)
                .NumberFormat = "mmm yy"
                .HorizontalAlignment = xlCenter
                .Interior.ColorIndex = 37
                    With .Font
                        .Name = "Lucida Sans"
                        .Bold = True
                        .Size = 10
                    End With
            End With

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