Macro to display the first and last number in a sequence of numbers increased by 1

[mir994stan]

I started a new topic because the moderator suggested it to me. You can see the old one at this link. Old topic
I have a problem with the code I am currently using to make a short string of box numbers that I use in shipping. Instead of writing all the box numbers, I write from the first to the last and the minus sign indicates all the boxes in between. The code works perfectly in most cases, but if there are several consecutive unique numbers I get an run time error 9 Subscript out of range because it cannot make a sequence with them. 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 //

I would be very grateful if someone could help. Thanks in advance!

This is my current code:

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 
                    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]

Ok you said consecutive 'numbers from M00100 to M00150 it should write M00100-150'.

Explain what the non consecutive numbers should display. Do you want to display //boxnumber and then start checking the next boxnumber for consecutive numbers?

Examples:
M00100 to M00150 it should write M00100-150
M00152 should write //M00152
M00154 should write //M00154
M00157 to M00159 should write M00157-159

Is that correct?

 

[Fluff]

This is my current code:

So you totally ignored my suggestion that fixed your initial problem & have gone back to the code that doesn't work at all.

 

[mir994stan]

Ok you said consecutive 'numbers from M00100 to M00150 it should write M00100-150'.

Explain what the non consecutive numbers should display. Do you want to display //boxnumber and then start checking the next boxnumber for consecutive numbers?

Examples:
M00100 to M00150 it should write M00100-150
M00152 should write //M00152
M00154 should write //M00154
M00157 to M00159 should write M00157-159

Is that correct?

Your example is correct. And with non consecutive numbers should look like this: M00100-150 // M00200 // M00220-225 // M00233 // M00257 // M00267

 

[mir994stan]

So you totally ignored my suggestion that fixed your initial problem & have gone back to the code that doesn't work at all.

Because it could lead someone in my opinion to the bad track, because the code doesn't make an error but makes a bad result ... My code made either a good result or an error

 

[johnnyL]

According to your file you uploaded ... would the following F9 result be acceptable? :

//M004689552//M004704396//M004704399-401//M004705802//M004733870//M004736913-916  Broj serije()

 

[Peter_SSs]

Will it always be 3 digits after the dash?
Or could it be that you had
M004704998, M004704999,M004705000,M004705001 meaning you might need 4 digits after the dash in your summary?
M004704998-5001

If it is not always 3, then instead of
M004736913-916
could we just have this?
M004736913-6

 

[johnnyL]

Original file size = 5.47MB
File that should work = 33kb

Let us know.

 

[johnnyL]

Will it always be 3 digits after the dash?
Or could it be that you had
M004704998, M004704999,M004705000,M004705001 meaning you might need 4 digits after the dash in your summary?
M004704998-5001

If it is not always 3, then instead of
M004736913-916
could we just have this?
M004736913-6

Heck no you can't have that!
Would that -6 represent 16, 26,36, etc?

 

[Peter_SSs]

Original file size = 5.47MB
File that should work = 33kb

Let us know.

Is that providing sample data on behalf of the OP or is it a file that contains a suggested solution, or something else?