Excel - Escribir tres letras y que aparezca número correlativo

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Fila As Long, Letras As String, a As Long, Num As Long

    ' ---&--- Si no es la columna A sale

    If Target.Column > 1 Then Exit Sub

    ' ---&--- Como minimo tiene que haber 3 caracteres

    If Len(Range(Target.Address)) < 3 Then Exit Sub

    ' ---&--- Guarda la fila y las Letras

    Fila = Target.Row

    Letras = Left(Target.Text, 3)

    ' ---&--- Busca hacia atras la ultima a partir de la fila 3

    '         Si esta en la fila 2 se ha de poner el numero a mano

    '         Si el numero tiene CEROA a la izquierda se tendra que usar el FORMAT

    Application.EnableEvents = False

    For a = Fila - 1 To 2 Step -1

        If Len(Cells(a, 1)) > 0 Then

           Num = Val(Mid$(Cells(a, 1), 4)) + 1

           Range(Target.Address) = Letras & Num

           Exit For

        End If

    Next

    Application.EnableEvents = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

   On Error Resume Next

   If Target.Row >= 2 And Len(Target.Value) = 3 Then _

      If IsNumeric(Mid(Cells(Target.Row - 1, 1), 4)) Or Target.Row = 2 Then _

         Target.Value = UCase(Target.Value) & IIf(Target.Row > 2, Format((Val(Mid(Cells(Target.Row - 1, 1), 4)) + 1), "000000"), "000001")

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

   On Error Resume Next

   If Target.Row >= 2 And Len(Target.Value) = 3 And Target.Column = 1 Then _

      If IsNumeric(Mid(Cells(Target.Row - 1, 1), 4)) Or Target.Row = 2 Then _

         Target.Value = UCase(Target.Value) & IIf(Target.Row > 2, Format((Val(Mid(Cells(Target.Row - 1, 1), 4)) + 1), "000000"), "000001")

End Sub