automatically sequencing 06-2597 thru 06-2600
 

How do I automatic sequncing this numbers

automatically sequencing 06-2597 thru 06-2600
 

"
schreef in bericht
...
How do I automatic sequncing this numbers


In a module:

Public Function SEQUENCE(ByVal fromCell As String) As String
Dim nVal As Integer
nVal = CInt(Right(fromCell, 4))
While Right(nVal, 2) < "00"
nVal = nVal + 1
Wend
SEQUENCE = Left(fromCell, 3) & nVal
End Function


Or, if you want i.e. 06-2486 to be converted to 06-2400 because it's below
06-...500, then use this one.


Public Function SEQUENCE(ByVal fromCell As String) As String
Dim nVal As Integer
Dim i As Integer
nVal = CInt(Right(fromCell, 4))
i = 1
If Right(nVal, 3) < 500 Then i = -1
While Right(nVal, 2) < "00"
nVal = nVal + i
Wend
SEQUENCE = Left(fromCell, 3) & nVal
End Function

In your worksheet you can then use =SEQUENCE(A1)