vba to compare data between commas for duplicates

I have a userform textbox that will only accept numerical values separated by a comma.
I need codes to compare the entries between the commas for duplicates and to evaluate for a minimum and maximum numerical entry. (as in no zeros and no numbers greater than 244).
I know I can launch it using the textbox change event, I just can't figure out the vba.
jcgrooveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonCommented:
Please see this basic example. It could be improved, but demonstrates how to do the checks you require. It checks that values are numeric, integer, <244 and then checks for duplication against earlier elements.
Screenshot showing data, validation and final resultUserFormExample.xlsm

See the code:
Option Base 0
Option Explicit

Private Sub TextBox1_Change()
Dim allUnique As Boolean
Dim allValidated As Boolean
Dim arr As Variant
Dim dupeArr() As String
Dim x As Integer, y As Integer
Dim validated As String

allUnique = True
allValidated = True

'Clear validation results textbox
Me.TextBox2.Value = Null

Debug.Print Me.TextBox1.Value
arr = Split(Me.TextBox1.Value, ",")
For x = LBound(arr) To UBound(arr)
    validated = validate(CStr(arr(x)))
    If validated <> "Pass" Then allValidated = False Else
    Me.TextBox2.Value = Me.TextBox2.Value & vbCrLf & arr(x) & vbTab & " Item Validation: " & validated
    
    'Check all but first element for duplication
    If x > LBound(arr) Then
        ReDim dupeArr(0)
        For y = LBound(arr) To x - 1
            If arr(y) = arr(x) Then
                allUnique = False
                If Not dupeArr(UBound(dupeArr)) = "" Then
                    ReDim Preserve dupeArr(UBound(dupeArr) + 1)
                End If
                dupeArr(UBound(dupeArr)) = y + 1
            End If
        Next y
        If dupeArr(LBound(dupeArr)) <> "" Then
            Me.TextBox2.Value = Me.TextBox2.Value & " - DUPLICATE of element" & IIf(UBound(dupeArr) > 0, "s ", " ") & Join(dupeArr, ",")
        End If
    End If
Next x

If allUnique And allValidated Then
Me.TextBox3.Value = "Pass"
Else
Me.TextBox3.Value = "Fail"
End If
End Sub


Function validate(strInput As String) As String
validate = "Pass"
If Not IsNumeric(strInput) Then
    validate = "Fail - not numeric"
ElseIf strInput <> Trim(strInput) Then
    validate = "Fail - contains spaces"
ElseIf Not strInput < 244 Then
    validate = "Fail >243"
ElseIf InStr(1, strInput, "0") > 0 Then
    validate = "Fail - contains zero"
ElseIf CInt(strInput) <> CDbl(strInput) Then
    validate = "Fail - not integer"
End If
End Function

Open in new window

0
jcgrooveAuthor Commented:
Thanks for the reply! I was able to get your code to work with very minor tweaking.
One question: how can we get the output to be failures only? There could be hundreds of inputs that pass validation and a handful that fail.
0
SimonCommented:
You can change these lines (22-23 in the listing above) to the version as shown below.
   If validated <> "Pass" Then 
     allValidated = False
    Me.TextBox2.Value = Me.TextBox2.Value & vbCrLf & arr(x) & vbTab & " Item Validation: " & validated
end if

Open in new window

Basically, the change is to include the output to textbox2 in the conditional based on the validation result.

If you just want the final result, you can comment out all references to me.textbox2
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

jcgrooveAuthor Commented:
After incorporating that last change, the duplicate check does not show the user input that was duplicated anymore.
0
SimonCommented:
Sorry, I was too quick with that edit.

Here's a revised version of the whole sub
Private Sub TextBox1_Change()
Dim allUnique As Boolean
Dim allValidated As Boolean
Dim arr As Variant
Dim dupeArr() As String
Dim x As Integer, y As Integer
Dim validated As String
Dim dupeMsg As String

allUnique = True
allValidated = True

'Clear validation results textbox
Me.TextBox2.Value = Null

Debug.Print Me.TextBox1.Value
arr = Split(Me.TextBox1.Value, ",")
For x = LBound(arr) To UBound(arr)
    dupeMsg = "" 'reset to empty string at start of each element check
    validated = validate(CStr(arr(x)))
    If validated <> "Pass" Then
        allValidated = False
        'Me.TextBox2.Value = Me.TextBox2.Value & vbCrLf & arr(x) & vbTab & " Item Validation: " & validated
    End If
    'Check all but first element for duplication
    If x > LBound(arr) Then
        ReDim dupeArr(0)
        For y = LBound(arr) To x - 1
            If arr(y) = arr(x) Then
                allUnique = False
                If Not dupeArr(UBound(dupeArr)) = "" Then 'only add new array element if initial element is not empty
                    ReDim Preserve dupeArr(UBound(dupeArr) + 1)
                End If
                dupeArr(UBound(dupeArr)) = y + 1
            End If
        Next y
        If dupeArr(LBound(dupeArr)) <> "" Then
            dupeMsg = " - DUPLICATE of element" & IIf(UBound(dupeArr) > 0, "s ", " ") & Join(dupeArr, ",")
        End If
    End If
    If validated <> "Pass" Or dupeMsg <> "" Then
        Me.TextBox2.Value = Me.TextBox2.Value & vbCrLf & arr(x) & vbTab & " Item Validation: " & validated
    End If
    If dupeMsg <> "" Then
        Me.TextBox2.Value = Me.TextBox2.Value & dupeMsg
    End If
Next x

If allUnique And allValidated Then
Me.TextBox3.Value = "Pass"
Else
Me.TextBox3.Value = "Fail"
End If
End Sub

Open in new window

0
jcgrooveAuthor Commented:
That got it! Thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.