Hi
If I get strings such as "14.3.2c", "14.3.3", etc. is it possible to workout the next sequence from the original string? For example,
if 14.3.2c comes in then the next must be 14.3.2d or
if 14.3.3 comes in then the next must be 14.3.3a or
if 14.3.3z comes in then the next must be 14.3.4
Thanks.
Robie
Identify next sequence
-
- Administrator
- Posts: 78494
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Identify next sequence
Do you want to do this using VBA? Or using field codes?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 656
- Joined: 18 Feb 2010, 14:26
Re: Identify next sequence
VBA please.HansV wrote:Do you want to do this using VBA? Or using field codes?
Thanks.
-
- Administrator
- Posts: 78494
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Identify next sequence
Here is a function you can use:
Example of usage:
Code: Select all
Function GetNext(seq As String) As String
Dim strLeft As String
Dim strRight As String
Dim lngPos As Long
strLeft = Left(seq, Len(seq) - 1)
strRight = Right(seq, 1)
Select Case strRight
Case "0" To "9"
GetNext = seq & "a"
Case "z"
lngPos = InStrRev(strLeft, ".")
If lngPos = 0 Then
GetNext = strLeft + 1
Else
GetNext = Left(strLeft, lngPos) & (Mid(strLeft, lngPos + 1) + 1)
End If
Case Else
GetNext = strLeft & Chr(Asc(strRight) + 1)
End Select
End Function
Code: Select all
Dim strOld As String
Dim strNew As String
strOld = "14.3.2a"
strNew = GetNext(strOld)
Debug.Print strNew
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 656
- Joined: 18 Feb 2010, 14:26
Re: Identify next sequence
Wow Hans.HansV wrote:Here is a function you can use:
Example of usage:Code: Select all
Function GetNext(seq As String) As String Dim strLeft As String Dim strRight As String Dim lngPos As Long strLeft = Left(seq, Len(seq) - 1) strRight = Right(seq, 1) Select Case strRight Case "0" To "9" GetNext = seq & "a" Case "z" lngPos = InStrRev(strLeft, ".") If lngPos = 0 Then GetNext = strLeft + 1 Else GetNext = Left(strLeft, lngPos) & (Mid(strLeft, lngPos + 1) + 1) End If Case Else GetNext = strLeft & Chr(Asc(strRight) + 1) End Select End Function
Code: Select all
Dim strOld As String Dim strNew As String strOld = "14.3.2a" strNew = GetNext(strOld) Debug.Print strNew
That is fantastic. Works wonderfully well. I thought it would be such a huge task as I just couldn't get my head around it.
What can I say? A big THANKS.