Based on the initial post, I created a function to check proposed serial numbers against your criteria.
Here is code for checking serial numbers and a test routine.You could use similar approach to deal with revisions.
Code:
' ----------------------------------------------------------------
' Procedure Name: checkSerial
' Purpose: Routine to check a string for length and excluded characters
'Serial numbers are 6-10 chars in length, and may include
'B,C,D,F,G,H,J,K,L,M,N,P,R,T,V,W,X,Y,0,1,2,3,4,5,6, 7,8,9,
'Excluding A,E,I,O,Q,S,U,Z
' Procedure Kind: Function
' Procedure Access: Public
' Parameter serNo (String): String/Serial number to check
' Return Type: String
' Author: Jack
' Date: 13-Mar-24
' ----------------------------------------------------------------
Function checkSerial(serNo As String) As String
Dim i As Integer
Dim ErrCondition As String
10 ErrCondition = ""
'check for length
20 If (6 > Len(serNo) Or 10 < Len(serNo)) = True Then
30 ErrCondition = ErrCondition & " -invalid length; "
' Debug.Print serNo & " has invalid length" 'for testing
40 End If
50 For i = 1 To Len(serNo)
'check for excluded chars
60 If InStr("AEIOQSUZ", Mid(serNo, i, 1)) > 0 Then
70 ErrCondition = ErrCondition & Mid(serNo, i, 1) & ", "
' Debug.Print Mid(serNo, i, 1) & " is illegal character" 'for testing
80 Else
' Debug.Print Mid(serNo, i, 1) & " is OK" 'for testing
90 End If
100 Next i
110 If Len(ErrCondition & "") = 0 Then
120 checkSerial = UCase(serNo)
130 ElseIf InStr(ErrCondition, ",") > 0 Then
140 ErrCondition = ErrCondition & " -illegal character(s)"
150 checkSerial = ErrCondition
160 Else
170 checkSerial = ErrCondition
180 End If
End Function
Test routine
Code:
Sub TestCheckSerial()
Dim i As Integer
Dim serials(4) As String
10 serials(0) = "Cyrr888"
20 serials(1) = "Seri888"
30 serials(2) = "2lng8889998YOU33"
40 serials(3) = "iou"
50 serials(4) = "123456789"
60 For i = LBound(serials) To UBound(serials)
70 Debug.Print serials(i) & " " & checkSerial(serials(i))
80 Next i
End Sub
Results of test
Code:
Cyrr888 CYRR888
Seri888 S, e, i, -illegal character(s)
2lng8889998YOU33 -invalid length; O, U, -illegal character(s)
iou -invalid length; i, o, u, -illegal character(s)
123456789 123456789