Numbering my cells and resetting by year

Question

This is my first post for and my first online programming question.

I am developing a program for recording project applications (Work Orders) at the factory. The current system is being deleted due to a switch from Windows XP.

I had a problem with the development of the numbering code.

Code format: YY - ######. I am currently using:

.Cells(iRow, 1).Value = Format(Date, "yy") & " - " & Format(iRow - 1, "000000")

Thus, to enter Year and "iRow" - 1 in the first column. iRow is defined using:

iRow = 2 'starting index for next empty WONum cell
With WORecordSheet
    'Find the next empty WONum cell
    Do While .Cells(iRow, 1).Value <> ""
        .Cells(iRow, 1).Activate
        iRow = iRow + 1
    Loop
End With

It scrolls each one until an empty row is found, then enters a number using the year and the specified row minus one in this cell.

The problem I am facing is the year. When the new year arrives, he needs to return to the first work order. For instance:

14 - 001111

14 - 001112

14 - 001113

15 - 000001

15 - 000002

, , . , , 1 , , , , , , , , iRow. , ... - "YY" "######"? if, "YY" , 1 "######"?

!

UPDATE: , . @user1759942. dim- "Right". :

Dim iRow As Long
Dim yr As Long
Dim lCodeNum As Long

WORecordSheet.Activate
iRow = 2 'starting index for next empty WONum cell
lCodeNum = 1 'starting index for last 6 digits of wonum
With WORecordSheet
'Find the next empty WONum cell
    Do While .Cells(iRow, 1).Value <> ""
        .Cells(iRow, 1).Activate
        iRow = iRow + 1
    Loop

    yr = Left(.Cells(iRow - 1, 1).Value, 2)

    If yr = Format(Date, "yy") Then
        lCodeNum = Right(.Cells(iRow - 1, 1).Value, 6) + 1
    Else
        lCodeNum = 1
    End If
End With

:

    .Cells(iRow, 1).Value = Format(Date, "yy") & " - " & Format(lCodeNum, "000000")

Answer 1

, . YY .

- dim yr as string, , : dim lCodeNum as long, while

dim yr as string
iRow = 2 'starting index for next empty WONum cell
lCodeNum = 1
With WORecordSheet
    'Find the next empty WONum cell
    yr = left(.cells(iRow,1).value, 2) 'gets the first 2 characters in the cell
    Do While .Cells(iRow, 1).Value <> ""
        .Cells(iRow, 1).Activate
        iRow = iRow + 1
        if not left(.cells(iRow, 1).value, 2) = yr then
            lCodeNum = 1
        else
            lCodeNum = lCodeNum + 1
        end if
        yr = left(.cells(iRow,1).value, 2)

    Loop
End With

:

    .Cells(iRow, 1).Value = Format(Date, "yy") & " - " & Format(lCodeNum, "000000")

, irow , lCodeNum , , 1 , .

irow lcodenum, 1, , , ,

14 - 000144
14 - 000145
15 - 000001
15 - 000147

Answer 2

100%, , , , . , , . , ...

, VBA - :

Dim yearDigits As Long
yearDigits = Right(Year(Now), 2)

, 2 , YY - ######, - :

Dim invoice As String

yearDigits = Right(year(Now), 2)

invoice = "13 - 001111"

invoiceYear = Left(invoice, 2)

:

Sub test()
Dim yearDigits As Long
Dim invoice As String
Dim invoiceYear As Long
Dim equalYear As Boolean

yearDigits = Right(year(Now), 2)

invoice = "13 - 001111"

invoiceYear = Left(invoice, 2)

equalYear = yearDigits = invoiceYear

MsgBox "Current Year = " & yearDigits & ".  Invoice Year = " & invoiceYear & ".  Are they equal? " & equalYear

End Sub

, , , , .

!

Answer 3

This should put a new code number in column A every time it starts. He should start sequencing the year shift again:

Sub FillId()
    Dim N As Long, Prev As String, PrevYr As Long, PrevNum As Long
    Dim Yr As Long, M As Long
    If Range("A1").Value = "" Then
        Range("A1").Value = Mid(Year(Date), 3) & " - " & "000001"
        Exit Sub
    End If

    N = Cells(Rows.Count, "A").End(xlUp).Row
    Prev = Cells(N, "A").Value
    PrevYr = CLng(Mid(Prev, 1, 2))
    PrevNum = CLng(Mid(Prev, 6))
    Yr = CLng(Mid(Year(Date), 3))
    If Yr <> PrevYr Then
        M = 1
    Else
        M = PrevNum + 1
    End If
    Cells(N + 1, "A").Value = Mid(Year(Date), 3) & " - " & Format(M, "000000")
End Sub