Increase integer by +1 based on date

I want to increase all values in a column by 1 on the 1st of each month (E.g. when it becomes the 1st May, the first value in my column will go from 10 to 11).
Louise HitchcockAsked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
Try this. It will add to the tenures and change the column heading to reflect the month name when the workbook is opened on any day of a new month. I manually changed the heading to show "February" and so when you open this workbook it will be changed to "March". It will continue to say "March" until you open it in April.
29091790.xlsm
0
 
Rgonzo1971Commented:
HI,

you could use a formula
=IF(MONTH(A3)=MONTH(A2),B2,B2+1)

Open in new window

or try
Sub macro()
For Each c In Range(Range("A2"), Range("A" & Rows.Count))
    If Month(c.Offset(-1).Value) = Month(c.Value) Then
        c.Offset(, 1).Value = c.Offset(-1, 1).Value
    Else
        c.Offset(, 1).Value = c.Offset(-1, 1).Value + 1
    End If
    
Next
End Sub

Open in new window

Regards
0
 
Louise HitchcockAuthor Commented:
I'm afraid I couldn't get this to work. Essentially I have a column of numbers which need to automatically increase by 1 on the 1st of each month. The numbers refer to how long someone has been with an organisation. Is there a way to do this automatically using VBA or a formula? I don't have the months written down on the excel sheet at present, just the column with the numbers in.

Thank you in advance for any further guidance!
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Rgonzo1971Commented:
Could you send a dummy?
0
 
Louise HitchcockAuthor Commented:
I've uploaded a dummy file, however there's not much to go on as it's just a column of data at present!

Thanks!Tenure.xlsx
0
 
Rgonzo1971Commented:
Not enough information
0
 
Louise HitchcockAuthor Commented:
Unfortunately I cannot provide more information as that's the data I'm working with. Every time I open the file, I need the file itself to recognise when we've hit the first of the month so that it can increase the integer by 1. Not sure what else I can provide to help with this?

Thanks
0
 
Louise HitchcockAuthor Commented:
That's great thank you. Can you explain what this (C1048576) refers to in the VBA code? Just trying to understand it a little better.

Thanks for your help.
Louise
0
 
Martin LissOlder than dirtCommented:
It is the address of the last possible cell in column "C". It is used to help determine the last row with data in a column. There are several other ways to do it including
lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row

and

lngLastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

and

lngLastRow = Activesheet.UsedRange.Rows.Count

Excel isn't very good at keeping track of the last row that actually has data, and formatting of "unused" cells can fool it, so at times you may have to use one of the other calculations.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.