• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

*Automatically Resetting the Value of Invoice no to 1 whenever new month starts and updating the customer ID as per new invoice no.*


New Member
*different cell and it's data*
"A2" is Date in "dd-mm-yyyy"
"B2" is Year - *Year("A2")*
C2 is last two digits of the year "20" - *Right("B2",2)*
D2 is my month "04" - *Month("A2")*
E2 cell is my invoice no.
F2 cell is my customer ID.
Range("E2").value = Range("C2"). text & Range("D2").text & Range("E2"). text

Range("F2").value = "C" & Range("E2"). value

I am working for January. then I need my invoice Number to increase by 1 for each new invoice.
after that, if the month changes to February then again my invoice Number should start from 1 and get the increase by 1 for each new invoice.

It should follow the same every time the month gets changed.


If A2 is 05-02-2020

then F2 should be C200201 for the first invoice in that month.
for second invoice C200202

and if the month changes to say March then the first value for F2 should be C200301.

Now as the value of cell "D2" is dependent upon cell "A2".

*I need to run a macro whenever the value of D2 changes.*
*I am only be changing the date, I don't want to change the value of cell "D2" manually.*
I am having issues with automatic invoice no update, i.e, cell E2 value.

*When the value of A2 gets changed, the macro should run automatically.
This macro will contain the set of codes for incrementing the value of cell "E2" by 1, which in turn will update the value of customer ID i.e, cell "F2" automatically*

what is the possible VBA code for this?
please reply...
Hope you got this.
Thank you.



Active Member
It doesn't appear that your description (rows & columns values) match with your worksheet.

Either that or I really don't understand your description.