基于上一个单元格值的VBA代码至序列号自动生成

Question

我通过在A列中添加最后一个单元格值来在自动生成序列号中面临问题。最后一个单元格值BA00934），但不理解为什么代码不像此图一样生成序列号 BA00936 。我什至没有收到任何错误消息。

我如何以适当的方式使用row_number或有其他选择可以达到预期结果？

我已经使用以下VBA代码通过添加到Lastrow来生成序列号。

Private Sub cmdadd_Click()
On Error Resume Next
ActiveSheet.Unprotect
Dim LastRow As Long
'for giving the serial number based on last cell value by adding plus one
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Range("A" & LastRow).Select
    Selection.AutoFill Destination:=Range("A" & LastRow & ":A" & LastRow + 1), Type:=xlFillDefault
    Range("A" & LastRow + 1).Select
End With

dattern_serial nos

Answer 1

假设字符串总是长7个字符，以5位数字结束

Private Sub cmdadd_Click()
On Error Resume Next 'This line skips errors... bad practice unless you really know what you are doing
On Error GoTo 0
Dim LastRow As Long
With Workbooks(REF).Sheets(REF) 'Always refer to the wb/ws, otherwise VBA will refer to the active wb/ws
    .Unprotect
    'for giving the serial number based on last cell value by adding plus one
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    prfx = Left(.Cells(LastRow, "A"),2) 'Gets the prefix by getting the 2 leftmost characters of the last filled cell
    nmbr = Right(.Cells(LastRow, "A"),5)+1 'Gets the last 5 characters, which is the number
    'Numbers don't have leading zeroes, so if the string did they have been stripped
    'This determines if that happened and if so, it adds as many leading zeroes as the number of characters is shorter than 5
    If Len(nmbr) < 5 Then 
        For i = 1 To (5 - Len(nmbr))
            nmbr = "0" & nmbr
        Next i
    End If
    .Cells(LastRow + 1, "A").Value = prfx & nmbr
End With
End Sub

Answer 2

Private Sub CommandButton1_Click()
    On Error Resume Next
    On Error GoTo 0
    Dim UsedRow As Long
    
    With ActiveSheet
    
    UsedRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    TextChar = Left(.Cells(UsedRow, "B"), 2)
    NumChar = Right(.Cells(UsedRow, "B"), 5) + 1
    
    If Len(NumChar) < 5 Then
    For m = 1 To (5 - Len(NumChar))
    NumChar = "0" & NumChar
    Next m
    End If
    
    .Cells(UsedRow + 1, "B").Value = TextChar & NumChar
    End With
End Sub