Is this number validation possible?
 

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

Is this number validation possible?
 

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

Is this number validation possible?
 

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


"Forum freak (at work)" wrote:

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

Is this number validation possible?
 

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


"Forum freak (at work)" wrote:

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

Is this number validation possible?
 

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(I SERROR(FIND("2",A1))),NOT(ISERROR(FIND("3",A1))),N OT(ISERROR(FIND("4",A1))),NOT(ISERROR(FIND("5",A1) ))),TRUE,FALSE)

Do I win?
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Forum freak (at work)" wrote:

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

Is this number validation possible?
 

No,

You could have done this

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

Mike

"Alan Moseley" wrote:

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(I SERROR(FIND("2",A1))),NOT(ISERROR(FIND("3",A1))),N OT(ISERROR(FIND("4",A1))),NOT(ISERROR(FIND("5",A1) ))),TRUE,FALSE)

Do I win?
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Forum freak (at work)" wrote:

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

Is this number validation possible?
 

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

"Mike H" wrote in message
...
No,

You could have done this

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

Mike

"Alan Moseley" wrote:

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(I SERROR(FIND("2",A1))),NOT(ISERROR(FIND("3",A1))),N OT(ISERROR(FIND("4",A1))),NOT(ISERROR(FIND("5",A1) ))),TRUE,FALSE)

Do I win?
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Forum freak (at work)" wrote:

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

Is this number validation possible?
 

On Thu, 23 Oct 2008 04:14:01 -0700, Mike H
wrote:

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

Is this number validation possible?
 

Many thanks Ron

Luckily I am just using the formula in a cell!

Regards
Kenny W

"Ron Rosenfeld" wrote in message
...
On Thu, 23 Oct 2008 04:14:01 -0700, Mike H

wrote:

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