reset counter change of year

[orsm6]

Hi all - i am trying to improve a register that is used at work. in column A a macro inserts into the next blank cell a unique ID e.g. PCR21040.

PCR = hard coded - never changes
21 = current year - taken from last 2 digits of year in cell Q1
040 = is the last entry for that year (could be any amount... just depends)

how can i get the counter easily to reset to PCR22001 when the year changes? and obviously this happens each year that passes

TIA

 

[Toadstool]

Hi Orsm6,

Can't you just put the current year into Q1?

=YEAR(TODAY())

 

[orsm6]

Hi Orsm6,

Can't you just put the current year into Q1?

=YEAR(TODAY())

Hi Toadstool

that is what i currently have yes. that is not my concern though... my concern is how to reset the last 3 digits back to 001 when Q1 changes year. it's probably easy, i just cant see the solution lol

 

[Phuoc]

Try this

="PCR"&TEXT(Q1,"yy")&TEXT(SUMPRODUCT(--(YEAR($Q$1:Q1)=YEAR(Q1))),"000")

 

[orsm6]

Try this

="PCR"&TEXT(Q1,"yy")&TEXT(SUMPRODUCT(--(YEAR($Q$1:Q1)=YEAR(Q1))),"000")

the result when pasted into Q2 was PCR05001

you can leave out the PCR and YY as i have obtained them other ways. but still trying to work out how to make it see that it is a new year and start from 001 again.

 

[Phuoc]

Can you share your code?

 

[orsm6]

Can you share your code?

Sub newPCRID()
'this will set up the next unique PCR number. it should align to current year, but keep the number of PCRs running.
    With Sheets("Register")
        Dim rw As Integer
       
        rw = .Range("A65536").End(xlUp).Row + 1
        .Range("A" & rw).value = "PCR" & Format(Date, "YY") & .Range("q1").Text
        .Range("q1").value = .Range("q1").value + 1


       
    End With
End Sub

this code finds next blank cell in column A - then it inserts the next unique number e.g. PCR22006
it then adds 1 to the number in Q1 to get it ready for the next unique number.... so if before running the macro it was 006... after running the macro Q1 would become 007.

it is resetting Q1 at the turn of the year back to 001 (without affecting any of the numbers already there) that i am trying to figure out.

 

[Tupe77]

Maybe something like this?

Sub TS_newPCRID()

Dim rw As Integer, TmpArr As Variant, item As Variant, MaxYear As Integer: MaxYear = 0

'this will set up the next unique PCR number. it should align to current year, but keep the number of PCRs running.
    With Sheets("Register")
        rw = .Range("A65536").End(xlUp).Row + 1
        TmpArr = .Range("A1" & ":" & "A" & rw).Value
        
        For Each item In TmpArr
            If InStr(item, "PCR") > 0 And MaxYear < Mid(item, 4, 2) Then MaxYear = Mid(item, 4, 2)
        Next item
        
        If Format(Date, "YY") > MaxYear Then .Range("q1").Value = 1
        .Range("A" & rw).Value = "PCR" & Format(Date, "YY") & .Range("q1").Text
        .Range("q1").Value = .Range("q1").Value + 1
    End With
End Sub

 

[orsm6]

Maybe something like this?

Sub TS_newPCRID()

Dim rw As Integer, TmpArr As Variant, item As Variant, MaxYear As Integer: MaxYear = 0

'this will set up the next unique PCR number. it should align to current year, but keep the number of PCRs running.
    With Sheets("Register")
        rw = .Range("A65536").End(xlUp).Row + 1
        TmpArr = .Range("A1" & ":" & "A" & rw).Value
       
        For Each item In TmpArr
            If InStr(item, "PCR") > 0 And MaxYear < Mid(item, 4, 2) Then MaxYear = Mid(item, 4, 2)
        Next item
       
        If Format(Date, "YY") > MaxYear Then .Range("q1").Value = 1
        .Range("A" & rw).Value = "PCR" & Format(Date, "YY") & .Range("q1").Text
        .Range("q1").Value = .Range("q1").Value + 1
    End With
End Sub

looks good, not sure how i can test it, but will plug it into the WB and see what happens.

 

[orsm6]

looks good, not sure how i can test it, but will plug it into the WB and see what happens.

it crashes at this line of code: MaxYear = Mid(item, 4, 2)

but with that said, I think this might need to be on WB open event to reset it back to 001 if the year is new.
running it at the time the code normally runs messes up the save file name that all of these things are working towards.