Problem with refreshing a number dpening on the date !

Posted by Chris Shepherd on January 19, 2002 3:13 AM

Im doing a project for college and i need to be able to refresh a number in a cell, when the year changes.

The project is about a transaction number, and the first part of it is made from a date, and then the second half is made by adding a number i.e 2002/1. Then when the button is pressed it changes to 2002/2. I have been able to do the transaction number, but the problem is that when the year chages i.e. from 2002 - 2003 the number at the end needs to start at 1 again !! Please could you advise on any way that this could be done!! Thank you very much, Chris Shepherd

super_shepy@hotmail.com

Posted by Tom Urtis on January 19, 2002 7:48 AM

Here's one way to do it

OK, this looks a bit awkward because it uses another cell, but it worked when I tested it.

Let's say this transaction number is housed in A1. In some other cell (let's use B1 for this example), enter the formula

=IF(ISNUMBER(SEARCH("/",A1)),RIGHT(A1,LEN(A1)-SEARCH("/",A1)),A1)

This will return the numeral(s) to the right of the transaction number's dash.

Now, attach this macro to your button:

Sub IncreaseTransNum()
If Left(Range("A1"), 4) = Format(Date, "yyyy") Then
Range("A1").Value = Left(Range("A1"), 4) & "/" & Range("B1").Value + 1
Else
Range("A1").Value = Format(Date, "yyyy") & "/" & 1
End If
End Sub

Go ahead and change the year on your system date settings and test the macro, to be sure it does what you want, and to be sure that I interpreted your question correctly.

Hope this gets you an A in your college project!!

Tom Urtis