Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation of UK VAT Number | Excel Worksheet Functions | |||
validation for phone number | Excel Discussion (Misc queries) | |||
attach a label to number, like validation but follows the number | Excel Worksheet Functions | |||
Whole Number Validation in Input Box | Excel Programming | |||
Number Validation | Excel Programming |