CODEWITHSUNDEEP
vbasequence
cosmarchy
cosmarchy

Reputation: 686

How to get the first and last numbers in a sequence

I'm working with VBA to try and get the first and last numbers in a delimited numerical sequence.

Data is presented as a comma delimited sequence of numbers only. They are always in numerical ascending order.

So an example of this might be:

1,2,3,5,7,8,9

What is required is the first and last of the sequences only, with a sequence defined as being numbers that are one more than the previous number, in this case there are two sequences: 1,2,3 and 7,8,9. 5 is not in the sequence since it it separated by more than one before and after it (4 and 6 are missing).

In these two cases, I need the first and last numbers of each sequence ie 1 & 3 for the first sequence and 7 & 9 for the second sequence.

I wonder whether regex would lend itself to this or whether it would be better to put them in to some collection to be able to navigate backwards and forwards more easily?

Not sure where to start really - any ideas?

Many thanks.

Upvotes: 0

Views: 767

Answers (3)

Brian M Stafford
Brian M Stafford

Reputation: 8868

This method takes an array containing numerical sequences and returns a collection containing the first and last number of every sequence found:

Option Explicit

Private Sub Form_Load()
   Dim c As Collection
   Set c = FirstAndLast(Array(1, 2, 3, 5, 7, 8, 9))
End Sub

Public Function FirstAndLast(ByVal Sequence As Variant) As Collection
   Dim i As Integer
   
   Set FirstAndLast = New Collection
   
   For i = LBound(Sequence) To UBound(Sequence) - 1
      If Sequence(i) + 1 = Sequence(i + 1) Then
         If FirstAndLast.Count Mod 2 = 0 Then FirstAndLast.Add Sequence(i)
         If i = UBound(Sequence) - 1 Then FirstAndLast.Add Sequence(i + 1)
      Else
         If FirstAndLast.Count Mod 2 = 1 Then FirstAndLast.Add Sequence(i)
      End If
   Next
End Function

Upvotes: 0

BDra
BDra

Reputation: 506

The easiest approach may be to use Split:

NumberSequence = "1,2,3,5,7,8,9"
ValueList = Split(NumberSequence, ",")

Then pull the values of the first and last index in the ValueList array.

The complication in your problem description is to recognize when there are "missing" numbers in the number sequence, but Split will help solve this issue too. After reading the numbers into an array it is easy enough to loop through them and determine where the "gaps" are.

This illustrates the method:

Sub SampleProject()
    
    GetMinAndMax "1,2,3,5,7,8,9"

End Sub

Sub GetMinAndMax(NumberSequence As String)
    
    ValueList = Split(NumberSequence, ",")
    
    MinValue = CInt(ValueList(0))
    
    For i = 1 To UBound(ValueList)
        If CInt(ValueList(i)) > CInt(ValueList(i - 1)) + 1 Then
            MaxValue = CInt(ValueList(i - 1))
            
            'Ignore single numbers ("5" in your example)
            If Not MinValue = MaxValue Then MsgBox MinValue & " - " & MaxValue
            
            MinValue = CInt(ValueList(i))
            
        ElseIf i = UBound(ValueList) Then
            MaxValue = CInt(ValueList(UBound(ValueList)))
            MsgBox MinValue & " - " & MaxValue
            
        End If
    Next

End Sub

Upvotes: 1

FaneDuru
FaneDuru

Reputation: 42236

Try the next function, please:

Function SequenceMargins(s As String) As Variant
  Dim arr As Variant, i As Long, strSeq As String, firstEl As Long
  Dim arrFin As Variant, k As Long
  
  arr = Split(s, ",")
  ReDim arrFin(UBound(arr))

  For i = 0 To UBound(arr) - 1
    If CLng(arr(i)) = CLng(arr(i + 1)) - 1 And firstEl = 0 Then firstEl = CLng(arr(i))
    If firstEl <> 0 And CLng(arr(i)) > firstEl And CLng(arr(i)) <> CLng(arr(i + 1)) - 1 Then
        arrFin(k) = firstEl & "&" & arr(i): k = k + 1
        firstEl = 0
    End If
    If i = UBound(arr) - 1 And firstEl <> 0 Then
        arrFin(k) = firstEl & "&" & arr(UBound(arr)): k = k + 1
    End If
  Next i
  ReDim Preserve arrFin(k - 1)
  SequenceMargins = arrFin
End Function

It can be called in this way:

Sub testSequenceMargins()
  Dim s As String, arr As Variant
  s = "1,2,3,5,7,8,9,10,13,14,15,20,34,35,36"
  arr = SequenceMargins(s)
  Debug.Print UBound(arr), arr(0), arr(UBound(arr))
End Sub

Upvotes: 0

Related Questions