Trouvez le prochaine numéro

' Fonction pour trouver le prochaine numéro de DA

Function incremente_numeroDA() As String

    Dim lastUsedRow As Long

    Dim lastDA As String

    Dim last_numDA As Variant

    Dim i As Long: i = 0

   

    lastUsedRow = Cells(Rows.Count, 2).End(xlUp).Row

    lastDA = Cells(lastUsedRow, "B").Value ' extrait le nom complet de la derniere DA

   

    Do

    lastDA = Cells(lastUsedRow - i, "B").Value

    i = i + 1

    Loop While Left(lastDA, 6) <> "PDR_IT"

   

        last_numDA = Mid(lastDA, 10, 4) ' extraction des 4 derniers chiffres

        last_numDA = last_numDA + 1 ' ajoute + 1 à la DA

   

        Select Case Len(last_numDA) ' mesure la chaine après le +1 et renvoi le nouveau numéro

            Case Is = 1

                incremente_numeroDA = " PDR_IT_" & Right(Year(Date), 2) & "000" & last_numDA

            Case Is = 2

                incremente_numeroDA = " PDR_IT_" & Right(Year(Date), 2) & "00" & last_numDA

            Case Is = 3

                incremente_numeroDA = " PDR_I _" & Right(Year(Date), 2) & "0" & last_numDA

            Case Is = 4

                incremente_numeroDA = " PDR_IT_" & Right(Year(Date), 2) & last_numDA

        End Select

   

End Function