autonumber depends upon start date

[ajm]

in an earlier post (http://www.vbaexpress.com/forum/showthread.php?t=30241), mdmckillop and geekgirlau assisted me with an autonumbering macro :cloud9:.

i want to be a bit cheeky and base the final format of the autonumber on the start date of the project it identifies. that is, if the start date is in 2010, the autonumber will be "GP10-009" or if its a 2011 start, then the number would appear as "GP11-006".

two things, how do i incorporate the year number into the autonumber, and secondly, can it be set up that if there are a swag of 2010 projects and then a 2011 project gets set up, that 2011 project should be the next incrementing 2011 project number and not just the next 2010 number with GP11 in front.

so from above, if we were up to GP10-009 and a 2011 project was entered, the number should be GP11-001 (not GP11-010).

code from mdmckillop and geekgirlau is:
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'///add a project number when cell within range(s) is doubleclicked
Dim c As Range
Dim d As Range
Dim y As Integer

'///locate cell for project number
Set c = Intersect(Target, Range("RefNbrRg"))
If c Is Nothing Then Exit Sub
If c <> "" Then Exit Sub

Cancel = True 'Prevent going into Edit Mode

'///grab year number at left of c
Set d = c.Offset(0, -1)
y = Right(d.Value, 2)

' number has not already been assigned
If Cells(c.Row, 9).Value = 0 Then
' check that all required values have been entered
If Application.CountA(Cells(c.Row, 1).Resize(, 8)) = 8 Then
' get the nextnumber
Cells(c.Row, 9) = WorksheetFunction.Max(Range("RefNbrRg")) + 1
Cells(c.Row, 9).NumberFormat = """GP10-""000"
Else
MsgBox "Please complete ALL details of project"
End If
End If
End Sub

[Simon Lloyd]

This should do what you need:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'///add a project number when cell within range(s) is doubleclicked
Dim c As Range
Dim d As Range
Dim y As Integer

'///locate cell for project number
Set c = Intersect(Target, Range("RefNbrRg"))
If c Is Nothing Then Exit Sub
If c <> "" Then Exit Sub

Cancel = True 'Prevent going into Edit Mode

'///grab year number at left of c
Set d = c.Offset(0, -1)
y = Right(d.Value, 2)

' number has not already been assigned
If Cells(c.Row, 9).Value = 0 Then
' check that all required values have been entered
If Application.CountA(Cells(c.Row, 1).Resize(, 8)) = 8 Then
' get the nextnumber
Cells(c.Row, 9) = WorksheetFunction.Max(Range("RefNbrRg")) + 1
Cells(c.Row, 9).NumberFormat = """GP" & Right(Format(Date, "yyyy"), 2) & "-""000"
Else
MsgBox "Please complete ALL details of project"
End If
End If
End Sub

[ajm]

thanks simon. quick amendment:

Cells(c.Row, 9).NumberFormat = """GP" & Right(Format(Date, "yyyy"), 2) & "-""000"

should be:

Cells(c.Row, 9).NumberFormat = """GP" & Right(Format(d, "yyyy"), 2) & "-""000"

The way your code utilises the start date field is great.

however, it doesn't address the increment issue. i am trying to increment project numbers for any year.

GP10-001
GP10-002
GP10-003
GP11-001
GP11-002

If these were my projects for the year, and I have a new 2011 project to list, its number should be GP11-003. using the code kindly provided by mdmckillop, geekgirlau, and yourself, this next project number would be GP11-004.

How do i make the Max function take the start date into consideration? can you do a Maxif?

[Simon Lloyd]

Can you supply a dummy workbook with the same structured data?

[ajm]

here tis simon. thanks for the persistence

[Simon Lloyd]

Try this, double click as needed, it should do what you want!