VBA to increase Month shown in a cell by 1 Month

[Phil Payne]

Hello all,

A simple vba that works

Sub Add_One()
Range ("C10").value = Range ("C10").value+1
End sub

So from 999 to 1000 etc...OK but i need to increase by Month i.e. January 2024 moves to February 2024 etc. and number of days in a month vary.

How can I modify the code to change by calendar month over the years (at least to 2030)

 

[Peter_SSs]

i.e. January 2024 moves to February 2024

What exactly is in the cell?

• January 2024 as a Text value?
• 20 January 2024 as a Date but formatted to just show the month and year?
• Something else?

 

[Phil Payne]

Date as a numbe, referenced by a linked cell that is formatted to mmmm yyyy

 

[Peter_SSs]

Date as a numbe, referenced by a linked cell that is formatted to mmmm yyyy

Can it be any date? That is, could it be 31 January or 7 March etc or is it always the first day of the month?

.. and so it currently contains a formula and you want vba to replace that formula result with a new non-formula date in the following month?
If the date is currently 7 March 2023 do you want the new date to be 7 April 2023 or just any date in April since only month and year will be visible?
Give us as much detail as you can please.

 

[Phil Payne]

Hi Peter,

It can be any date as long as it presents the month and year only e.g. January 2024, February 2024 and so on.

At the moment I have code that modifies a column range of a formula to go to the range to the right.
The ranges are columns recording data on a calendar month basis and the cell on row 2 holds the Month in the format 01/01/2024 that is formatted to show 01 January 2024. Row 1 above is linked (=row2) and formatted to show January 2024 etc.

The ranges are on a sheet called "GPR_Data" and the result needs to show one cell of a sheet named "Actives"
I would like to have a piece of code I can add to the existing that will make the appropriate change at the same time.

I hope I've explained in sufficient detail.

Thanks for taking the time to help.

 

[Peter_SSs]

It can be any date

So if the date is 30 January 2025, what is your calculation for increasing that by 1 month?

 

[popipipo]

Try this

Sub Add_One()

Range("C10") = DateAdd("M", 1, Range("C10"))
End Sub

 

[Phil Payne]

Excellent Popipo,

Your solution worked!

Thankyou very much.