Sequencing Numbers Not Working

[rodl66]

I have an Excel 365 worksheet that has a button I want to use to run a macro. When clicked, I want the VBA code to look for column A of the first empty row and generate an MPF Number in the format “MPF-yyyy-####” where yyyy is the current year and “####” starts with “0001”. Each time the button is clicked, a new MPF Number that increases by 1 is entered into the next empty row, column A. When a new year starts, the number should reset at 0001 using the same format as before. The code I am trying follows; however, it keeps inputting MPF-2023-0001 each time instead of incrementing it to 0002, 0003, etc. Thank you in advance for your assistance.

Sub GenerateMPFNumber()
    Dim lastRow As Long, mpfNumber As String, year As String, number As String
   
    ’ Get the last used row in column A
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
   
    ’ Check if the year has changed
    If Format(Date, “yyyy”) <> Range(“A” & lastRow - 1).Value Then
        ’ Reset number to 1 if a new year starts
        number = “0001”
    Else
        ’ Increment the existing number by 1
        number = Format(CInt(Right(Range(“A” & lastRow - 1).Value, 4)) + 1, “0000”)
    End If
   
    ’ Get the current year
    year = Format(Date, “yyyy”)
   
    ’ Generate the MPF number
    mpfNumber = “MPF-” & year & “-” & number
   
    ’ Enter the MPF number in the next empty row, column A
    Range(“A” & lastRow).Value = mpfNumber
End Sub

 

[DanteAmor]

Try this:

Sub GenerateMPFNumber()
  Dim lastRow As Long, mpfNumber As String, nYear As String, number As String
  
  ' Get the last used row in column A
  lastRow = Cells(Rows.Count, 1).End(xlUp).Row
  
  ' Get the year from the previous line
  If InStr(1, Range("A" & lastRow).Value, "-") > 0 Then
    nYear = Split(Range("A" & lastRow).Value, " - ")(1)
  End If
  
  ' Check if the year has changed
  If Format(Date, "yyyy") <> nYear Then
    number = "0001"       ' Reset number to 1 if a new year starts
  Else
    ' Increment the existing number by 1
    number = Format(CInt(Right(Range("A" & lastRow).Value, 4)) + 1, "0000")
  End If
  
  ' Enter the MPF number in the next empty row, column A
  Range("A" & lastRow + 1).Value = "MPF - " & Format(Date, "yyyy") & " - " & number
End Sub

Regards
Dante Amor

 

[rlv01]

Try changing this

    If Format(Date, "yyyy") <> Range("A" & lastRow - 1).Value Then

to this

    If Format(Date, "yyyy") <> Mid(Range("A" & lastRow - 1).Value, 7, 4) Then

 

[rodl66]

Try this:

Sub GenerateMPFNumber()
  Dim lastRow As Long, mpfNumber As String, nYear As String, number As String
 
  ' Get the last used row in column A
  lastRow = Cells(Rows.Count, 1).End(xlUp).Row
 
  ' Get the year from the previous line
  If InStr(1, Range("A" & lastRow).Value, "-") > 0 Then
    nYear = Split(Range("A" & lastRow).Value, " - ")(1)
  End If
 
  ' Check if the year has changed
  If Format(Date, "yyyy") <> nYear Then
    number = "0001"       ' Reset number to 1 if a new year starts
  Else
    ' Increment the existing number by 1
    number = Format(CInt(Right(Range("A" & lastRow).Value, 4)) + 1, "0000")
  End If
 
  ' Enter the MPF number in the next empty row, column A
  Range("A" & lastRow + 1).Value = "MPF - " & Format(Date, "yyyy") & " - " & number
End Sub

Regards
Dante Amor

Sir,

You are a gentleman and a scholar, this worked. Thank you kindly.

 

[DanteAmor]

Im glad to help you.
Thank you for the feedback and your kind words.