Fuction for Quality Check

StudentofExcel

New Member
Joined
Dec 2, 2017
Messages
7
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Can you clarify something for me regarding the following conditions...


if next two caracthers (3rd & 4th) are not: "AA,BB,CC,DD,EE,FF,GG,RR" return "Bad"
1) Do those letters have to be upper case only or would "aa", "bb", etc. be alright (in other words, does letter case matter for the 3rd and 4th characters)?



if "Code number" is "Good" but it does contain anywhere in code number: "Err","Error","EER" it should return "Bad".
2) Can any other text appear after the 6th character except for those specific three words?

3) What if "ERR" or "ERROR" or "eer" appear after the 6th character (in other words, does letter case matter after the 6th character)?
 
Upvote 0
Thank you for your reply.

All letters should be in upper case if not then "Bad".

1. It does matter for first two characters it should be all uppercase "<g class="gr_ gr_37 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-del replaceWithoutSep" id="37" style="background-position: -1px calc(100% + 3px); color: rgb(34, 34, 34); text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: arial, sans-serif; font-size: small; font-style: normal; font-weight: 400; word-spacing: 0px; border-bottom-color: transparent; border-bottom-width: 2px; border-bottom-style: solid; display: inline; white-space: normal; orphans: 2; widows: 2; background-image: url("data:image/svg+xml;charset=utf8,%3Csvg xmlns='http://www.w3.org/2000/svg' width='100%' height='100%'%3E%3Cline opacity='0.75' x1='4' y1='100%' x2='100%' y2='100%' transform='translate(-1.5, -2.5)' stroke-width='3' stroke-linecap='round' stroke='%23fc5454'/%3E%3C/svg%3E"); background-repeat: no-repeat; background-size: calc(100% + 1px) 100%; background-color: rgb(255, 255, 255); font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial; animation: gr__appear_critical 0.4s ease forwards;" data-gr-id="37">Good",</g> if they are not then "Bad",
2. It can for Example "30RR44-W" this is "Good" but "30RR44-Err" this is "Bad" also this is Bad "30RR44-W-ERR",
3. It also matters after 6th characters for "ERR", "ER", "ERROR" to be all in upper case because of "30RR44-W" = Good.

I hope my answer helped.

Thank you for your time
 
Upvote 0
Hi,

as a startpoint:

Code:
function QC(rng as range) as boolean
with rng
if left(.value, 2)*1 mod 10 <> 10 then goto poor
if instr(1, "AABBCCDDEEFFGGRR", mid(.value3,2)) = 0 then goto poor
dd = mid(.value,5,2)*1
if not(isnumeric(dd) and left(dd,1) = right(dd,1)) then goto poor
if instr(1,.value, "err") > 0 or instr(1,.value, "eer") > 0 then goto poor

QC = true :exit function
poor:
end with
end function

regards
 
Upvote 0
Give this function a try...
Code:
[table="width: 500"]
[tr]
	[td]Function QC(S As String) As String
  QC = "Bad"
  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
    If InStr(1, S, "Err", vbTextCompare) = 0 And InStr(1, S, "Error", vbTextCompare) = 0 And InStr(1, S, "EER", vbTextCompare) = 0 Then QC = "Good"
  End If
End Function[/td]
[/tr]
[/table]
 
Upvote 0
Try something like this. You may need to tweak some of the logic, but it works for me based on my assumptions about what you want to do.

Option Explicit


Function QC(d)


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

test = True

test1 = True
test2 = True
test3 = True

d = CStr(d)

QC = "Bad"

If Len(d) < 6 Then test = False

str = Mid(d, 1, 2)
If Application.IsError(Application.Match(str, Array("10", "20", "30", "40", "50", "60", "70", "80", "90"), False)) Then test = False

str = Mid(d, 3, 2)
If Application.IsError(Application.Match(str, Array("AA", "BB", "CC", "DD", "EE", "FF", "GG", "RR", ""), False)) Then test = False

str = Mid(d, 5, 2)
If Application.IsError(Application.Match(str, Array("11", "22", "33", "44", "55", "66", "77", "88", "99"), False)) Then test = False

test1 = (Len(d) = Len(Application.Substitute(d, "Err", "")))
test2 = (Len(d) = Len(Application.Substitute(d, "Error", "")))
test3 = (Len(d) = Len(Application.Substitute(d, "EER", "")))

If Application.And(test1, test2, test3) Then test = False

If test Then QC = "Good"

End Function

I hope this helps.

Ken
 
Upvote 0
After looking over the compact answers that showed up while i was working on this, I realized I misread the error stuff. The function I posted returns Bad unless one of the Err words is present. Upon further review it seems that those strings trigger a "Bad" response, which seems more logical. So, the following should be better,

Function QC(d)


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

test = True

test1 = True
test2 = True
test3 = True

d = CStr(d)

QC = "Bad"

If Len(d) < 6 Then test = False

str = Mid(d, 1, 2)
If Application.IsError(Application.Match(str, Array("10", "20", "30", "40", "50", "60", "70", "80", "90"), False)) Then test = False

str = Mid(d, 3, 2)
If Application.IsError(Application.Match(str, Array("AA", "BB", "CC", "DD", "EE", "FF", "GG", "RR", ""), False)) Then test = False

str = Mid(d, 5, 2)
If Application.IsError(Application.Match(str, Array("11", "22", "33", "44", "55", "66", "77", "88", "99"), False)) Then test = False

test1 = (Len(d) <> Len(Application.Substitute(d, "Err", "")))
test2 = (Len(d) <> Len(Application.Substitute(d, "Error", "")))
test3 = (Len(d) <> Len(Application.Substitute(d, "EER", "")))

If Application.Or(test1, test2, test3) Then test = False

If test Then QC = "Good"

End Function
 
Upvote 0
Rick Rothstein

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

Thank you very much

__
Fennek

I have tested this and it returnes everything false.
__

Ken Cowen

I have tested it and it works, it's maybe better for me to use this function because I could understand it and I can add more "Codes".

Thank you all.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top