Code fails when generating sequence "Run-time error '9': Subscript out of range"

[mir994stan]

Hello to everyone,
I posted same question here but no response... Code fails when generating sequence "Run-time error '9': Subscript out of range"

I have a macro that generate short sequence of multiple box ID numbers. For example this box numbers: M005203031, M005203032, M005203033, M005268005, M005268006, M005268007, will turn into string like this: M005203031-033 // M005268005-007 and that works perfect! Sometimes i don t have all box numbers incremented by 1, numbers are randomly incremented, and error happends. As i could notice, error happens when next value in sequence is incremented more then 1 (one) compared to the previous value. For example in this case will error pop up: M005203031, M005203032, M005203034, M005203036, M005268006, M005268007. Because xx34+1 isn t equal to xx36. It would be great if this could be fixed.
Here is the code i have for this job. In debug mode this code line is highlighted

sequenceArr(counter) = arr(i + 1)

Sub Generisi()


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
    
    ReDim sequenceArr(1 To UBound(arr))
    sequenceArr(1) = arr(1)
    counter = 2
            For i = 1 To UBound(arr) - 1
                 If CLng(arr(i)) + 1 = CLng(arr(i + 1)) Then '<<< i think in this line here error is generated
                    tempLastElement = arr(i + 1)
                    sequenceArr(counter) = tempLastElement
             Else
                    counter = counter + 1
                    sequenceArr(counter) = arr(i + 1) '<<<this line here is highlighted
                    counter = counter + 1
            End If
        
    Next
    ReDim Preserve sequenceArr(1 To counter)
    result = ""
    counter = 1
    For i = 1 To UBound(sequenceArr) - 1
        If counter > UBound(sequenceArr) Then Exit For
        If result = "" Then
            result = letter & sequenceArr(counter) & "-" & Right(sequenceArr(counter + 1), 3)
            counter = counter + 2
        Else
            result = result & "//" & letter & sequenceArr(counter) & "-" & Right(sequenceArr(counter + 1), 3)
            counter = counter + 2
        End If
    Next
    ws.Range("C4").Value = result
    
    
    
End Sub

 

[johnnyL]

I tried your sugestion but, it doesn t fix problem. It just display as result first and last number of string, and nothing in beetwen :/

Is this the proper result for F9?

M004689552-396//M004704399-400//M004704401-802//M004733870-913//M004736914-915//M004736916-  Broj serije()

 

[mir994stan]

Okay i will post a new

Is this the proper result for F9?

M004689552-396//M004704399-400//M004704401-802//M004733870-913//M004736914-915//M004736916-  Broj serije()

Hmm, actually no, the result of the string is wrong because the minus sign indicates all the boxes between the number on the left and right side of the character. For example: // M004704401-802 // Like this here are listed all box numbers from M004704401 to M004704802 thats 400 boxes in between . For each box I own I have ID numbers that increase in ascending order. If I have 50 boxes and the first one starts with the number M00100 and each subsequent number is incremented by the 1. Code instead of writing all the numbers from M00100 to M00150 it should write M00100-150 as a result. And if there is an interrupt in the array of those 50 boxes, it should mark each interrupt with // and start checking the array again. Any number that cannot be incremented by 1 must be interrupted by //

 

[mir994stan]

I do understand, what you want, but as that is a completely different question, you will need to start a new thread. Thanks

I posted new thread, here is the link. Thanks New thread

 

[Fluff]

Thanks