Sequential numbers

Hi,


I am about to create a function that displays the list of OR Nos that has been cancelled in a message box, but what made this very tricky is that if the OR Nos cancelled is sequential , I have to display it in range, if not then display it one by one. I dont know how to start this :huh: can someone help me with it please. Thank you. :)

eg.

if cancelled OR is or 1,2,3,4,5 i have to display in this manner->
"1-5 OR has been cancelled"

if cancelled OR is 1,2,3,5,6,8 then i have to dispaly it in this manner -> 1
"1-3, 5-6 and 8 OR has been cancelled"

I am done with this function :) this case may now be closed. I thought theres a built in function i could use for this but there isnt. :chuckle:

Try this:

Dim i As Long
Dim lStart As Long, lEnd As Long
Dim sText As String
Dim aValues() As String, lValue As Long

aValues = Split("1,3,4,5,7,8,9,11,13,14", ",")

' The initial start/end value of the value
lStart = CLng(aValues(0))
lEnd = lStart

For i = 1 To UBound(aValues)
lValue = CLng(aValues(i))
If lValue = lEnd + 1 Then
' It's in the range
lEnd = lValue
Else
' The next value is not in the range, so add the last value or range
If Len(sText) = 0 Then sText = CStr(lStart) Else sText = sText & "," & CStr(lStart)
If lEnd <> lStart Then sText = sText & "-" & CStr(lEnd)

lStart = lValue
lEnd = lValue
End If
Next i

' Add the last value
If Len(sText) = 0 Then sText = CStr(lStart) Else sText = sText & "," & CStr(lStart)
If lEnd <> lStart Then sText = sText & "-" & CStr(lEnd)

Debug.Print sText

OOh very nice and clean. thanks flyguy