mir994stan
New Member
- Joined
- Jul 18, 2021
- Messages
- 42
- Office Version
- 2016
- Platform
- Windows
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:
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:
VBA 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
but they save me time.
