VBA宏处理非连续箱号序列报Run time error 9下标越界错误求助

• 数组容量不足：sequenceArr初始大小和输入ID数组arr完全一致，但每次遇到ID序列不连续时，counter会连续+2存储新序列的首尾值，当不连续片段数量较多时，counter会超出sequenceArr的初始下标上限，触发Run time error 9 下标越界错误。
• 原有逻辑未兼容单独ID场景：如果某段序列只有1个独立ID，后续拼接输出的逻辑会出现取值错误。

Sub Generate()
    Dim ws As Worksheet
    Dim arr() As String, result As String, letter As String, cellValue As String, tempLastElement As String
    Dim lastColumn As Long, counter As Long
    Dim firstColumn As Integer, targetRow As Integer, i As Integer
    Set ws = Worksheets("KreirajRadniNalog")
    firstColumn = 1
    targetRow = 1
    
    lastColumn = ws.Range(ws.Cells(targetRow, firstColumn), ws.Cells(targetRow, Columns.Count).End(xlToLeft).Columns).Count
    ReDim arr(1 To lastColumn - firstColumn + 1)
    letter = Left(ws.Cells(targetRow, firstColumn).Value, 1)
    For i = 1 To UBound(arr)
        cellValue = ws.Cells(targetRow, i).Value
        arr(i) = Right(cellValue, Len(cellValue) - 1)
    Next i
    
    ' 调整初始容量为2倍输入长度，足够存储所有序列的首尾值
    ReDim sequenceArr(1 To UBound(arr) * 2)
    sequenceArr(1) = arr(1)
    counter = 2
    tempLastElement = arr(1)
    
    For i = 1 To UBound(arr) - 1
        If CLng(arr(i)) + 1 = CLng(arr(i + 1)) Then
            tempLastElement = arr(i + 1)
            sequenceArr(counter) = tempLastElement
        Else
            ' 存储当前序列的尾值
            sequenceArr(counter) = tempLastElement
            ' 移动到下一个位置存储新序列的首值
            counter = counter + 1
            sequenceArr(counter) = arr(i + 1)
            tempLastElement = arr(i + 1)
            ' 移动到下一个位置准备存储新序列的尾值
            counter = counter + 1
        End If
    Next
    ' 修正最后一个序列的尾值存储
    sequenceArr(counter) = tempLastElement
    ReDim Preserve sequenceArr(1 To counter)
    
    result = ""
    counter = 1
    Do While counter <= UBound(sequenceArr)
        Dim startVal As String, endVal As String
        startVal = sequenceArr(counter)
        endVal = sequenceArr(counter + 1)
        If result <> "" Then result = result & " // "
        If startVal = endVal Then
            ' 单独ID直接输出
            result = result & letter & startVal
        Else
            ' 连续序列输出首尾缩写
            result = result & letter & startVal & "-" & Right(endVal, 3)
        End If
        counter = counter + 2
    Loop
    
    ws.Range("C4").Value = result
End Sub

1. 扩大了sequenceArr的初始容量，避免下标越界问题
2. 修正了序列首尾值的存储逻辑，确保最后一段序列也能正确输出
3. 增加了单独ID的判断逻辑，单个不连续ID无需加后缀，直接输出即可
4. 你提供的错误测试序列M0046552、M0047396、M0047399、M0047802、M0047803运行后输出结果为：M0046552 // M0047396 // M0047399 // M0047802-803，符合需求。

内容的提问来源于stack exchange，提问作者Mirko Stanisic