Is this number validation possible?

[Forum freak \(at work\)]

Hi

I need to validate the contents of a cell based on the following.

It must be a 5 digit number.
The 5 numbers can only be 1,2,3,4, & 5
Each number can only be used once.

This will be a test included in a macro however could it be done by formula?

Any help would be appreciated.

Kenny W
Using XP Pro and Excel 2003

 

[Incidental]

Hi Kenny

The code below is one way to do what you asked though it does contain
lots of loops so is not ideal though seems the most logical way to do
it in code to me at this moment. It will loop the used rows in column
1 and check the contents if it is 5 digits long it will begin to check
the numbers for 1 to 5 each time a number is found it will increament
a counter after checking all 5 digits i check the counter to see if it
is 5 which indicates that the numbers 1, 2, 3, 4, & 5 were found in
that cell.

Option Explicit
Dim lastRow As Integer
Dim cellLen As Integer
Dim iCell As Integer
Dim iNum As Integer
Dim iDigit As Integer
Dim nCounter As Integer

Private Sub CommandButton1_Click()

With ActiveSheet

lastRow = .UsedRange.Rows.Count

For iCell = 1 To lastRow

nCounter = 0

If Len(.Cells(iCell, 1).Value) = 5 Then

For iNum = 1 To 5

For iDigit = 1 To 5

If Mid(.Cells(iCell, 1).Value, iDigit, 1) = iNum Then

nCounter = nCounter + 1
Exit For

End If

Next iDigit

Next iNum

If nCounter = 5 Then

MsgBox "I fit the description"

End If

End If

Next

End With

End Sub

I hope this helps you out

Steve

 

[Joel]

Function ValidateNum(Target As Range)

Num = Target.Text
ValidateNum = True
If Len(Num) <> 5 Then
ValidateNum = False
Else
For i = 1 To 5
char = Mid(Num, i, 1)
If Asc(char) < Asc("0") Or _
Asc(char) > Asc("9") Then

ValidateNum = False
Exit For
End If
Next i
End If

End Function

 

[Mike H]

Hi,

Try this

Function IsValid(Target As Range)
MyString = Target.Text
IsValid = True
If Len(MyString) <> 5 Then
IsValid = False
Else
For x = 1 To 5
MyChar = Mid(MyString, x, 1)
newstring = WorksheetFunction.Substitute(MyString, Mid(MyString, x,
1), "")
If Asc(MyChar) < Asc("0") Or Asc(MyChar) > Asc("5") Or Len(newstring)
< 4 Then
IsValid = False
Exit For
End If
Next x
End If
End Function


Mike

 

[Alan Moseley]

In answer to your question, yes it can be done by a formula, which is
something like:-

=IF(AND(LEN(A1)=5,NOT(ISERROR(FIND("1",A1))),NOT(ISERROR(FIND("2",A1))),NOT(ISERROR(FIND("3",A1))),NOT(ISERROR(FIND("4",A1))),NOT(ISERROR(FIND("5",A1)))),TRUE,FALSE)

Do I win?

 

[Mike H]

No,

You could have done this

=IF(AND(LEN(A1)=5,NOT(ISERROR(FIND({"1","2","3","4","5"},A1)))),TRUE,FALSE)

Mike

 

[Forum freak \(at work\)]

Yes these did the trick.

As to "Who is the winner" What is the criteria for a winner, speed, maybe
accuracy.
Perhaps I should win with most gratitude!!!

Kenny W

 

[Ron Rosenfeld]

No,

You could have done this

=IF(AND(LEN(A1)=5,NOT(ISERROR(FIND({"1","2","3","4","5"},A1)))),TRUE,FALSE)

Mike

That's OK for just a formula in a cell, but you can't use an array constant in
a Data Validation custom formula.
--ron

 

[Forum freak \(at work\)]

Many thanks Ron

Luckily I am just using the formula in a cell!

Regards
Kenny W