Macro for a unique sequence number

Is there anyway to add a unique sequence number based on a given format.

For example

I input the 1st letter of the first name, first 4 of last name, month, and job number

Tom Jones, Month is January, 1st job

t_jone_01_01

next job would be

t_jone_01_02

Is there any code that would automatically increase the last two digits by one each time.....03,04....10,11....

Thanks for any help

Josh

Where do you want to do this?

You could create a custom function for this.


You would insert a new module into the workbook. In that new module you would put:

Function JobSequence(myVal As String) As String

FirstNum = Left(Right(myVal, 5), 2) + 1
If Len(FirstNum) = 1 Then FirstNum = "0" & FirstNum

SecondNum = Right(myVal, 2) + 1
If Len(SecondNum) = 1 Then SecondNum = "0" & SecondNum

JobSequence = Left(myVal, Len(myVal) - 5) & FirstNum & "_" & SecondNum

End Function


If the first entry was in "B2" and you wanted the next to be in B3, then in cell B3 you would put:

=JobSequence(B2)

- Pete