Fuction for Quality Check

[StudentofExcel]

Hello All,

If some come can point me to right resources I would appreciate it.

My problem is that I would like to create function =QC() that returns "Good" or "Bad".

And I would use it to check all "code numbers" if they have less then 6 character to return text "Bad" next to a cell. If first two caracthers are not: "10,20,30,40,50,60,70,80,90" it should return "Bad", if next two caracthers (3rd & 4th) are not: "AA,BB,CC,DD,EE,FF,GG,RR" return "Bad", if next two caracthers (5th & 6th) are not: "11,22,33,44,55,66,77,88,99" return bad, if "Code number" is "Good" but it does contain anywhere in code number: "Err","Error","EER" it should return "Bad".

Example

A (Code numbers) B

10CC22 Good
10CC22Err Bad
11CC22 Bad
30RR44 Good
30RR4 Bad

The function would be located in B2 and it would be based on A2, I would drag it to the end of a column of B. =QC(A2)
If someone could help I would very appreciate it.

Best regards,

Jenan

 

[Rick Rothstein]

Rick Rothstein

This seems to work Could you just advise me how to add future letters or numbers to this function.

This is the line of code that controls the pattern...

If S Like "[1-9]0[A-GR][A-GR][1-9][1-9]*" And Mid(S, 3, 1) = Mid(S, 4, 1) And Mid(S, 5, 1) = Mid(S, 6, 1) Then

the red text controls what letters or digits are acceptable and the green part controls the fact that certain characters must equal other characters. The green part should be self-explanatory, but the red part needs more explanation. The best I can do for you there is to direct you to the help page for the the Like operator. This web page covers the basics in decent detail and provides a fair number of examples to help you understand the flexibility possessed by the Like operator...

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/like-operator

 

[Ken Cowen]

The best way would probably be to add 4 dynamic ranges to you workbook and reference them for the MATCH functions. That way you can just add or delete from those ranges and your code will still work. You could probably build some of this into the more compact and probably much faster Functions provided by Rick and Fennek. Something like:


Function QC_2(d)


Dim test As Boolean
Dim test1 As Boolean, test2 As Boolean, test3 As Boolean
Dim str As Variant

Dim n As Integer

test = True

d = CStr(d)

QC_2 = "Bad"

If Len(d) < 6 Then test = False

str = CInt(Mid(d, 1, 2))
If Application.IsError(Application.Match(str, Range("NoCodes1"), False)) Then test = False

str = Mid(d, 3, 2)
If Application.IsError(Application.Match(str, Range("LetterCodes"), False)) Then test = False

str = CInt(Mid(d, 5, 2))
If Application.IsError(Application.Match(str, Range("NoCodes2"), False)) Then test = False

For n = 1 To Range("wrds").Rows.Count

str = Range("wrds").Cells(n, 1)

If (Len(d) <> Len(Application.Substitute(d, str, ""))) Then
test = False
GoTo 100
End If

Next n


100

If test Then QC_2 = "Good"

End Function