Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to validate entries in a defined range
I want to create a function that tests a range of cells stated to ensure
that the cells contain only valid data entries - lets call the function ValidateEntry. For my purposes valid data is any cell in the range whose value is either:- 1) Null (Blank or empty) 2) "X" (the letter X) 3) Begins with the letters "DC" and is immediately followed by 2 digits e.g DC09 4) Begins with the letters "SC" and is immediately followed by 2 digits e.g SC25 e.g ValidateEntry(A5:Z5) would return a "true" statues if all cells within that range met the criteria above, if not then it would return a "false" answer (I.e boolean argument). I want to then use this function in an If statement e.g If(ValidateEntry(A5:Z5),"Hooray!","Ooops!") Sorry - still learning!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to validate entries in a defined range
Function ValidateEntry(rng As Range) As Boolean
Dim cell As Range ValidateEntry = True For Each cell In rng If (IsEmpty(cell.Value) Or _ cell.Value = "" Or _ cell.Value = "X" Or _ (Len(cell.Value) 3 And Left(cell.Value, 2) = "DC" And _ IsNumeric(Mid(cell.Value, 3, 1)) And IsNumeric(Mid(cell.Value, 4, 1))) Or _ (Len(cell.Value) 3 And Left(cell.Value, 2) = "SC" And _ IsNumeric(Mid(cell.Value, 3, 1)) And IsNumeric(Mid(cell.Value, 4, 1)))) Then Else ValidateEntry = False Exit Function End If Next cell End Function -- HTH RP (remove nothere from the email address if mailing direct) "Lee Wold" wrote in message ... I want to create a function that tests a range of cells stated to ensure that the cells contain only valid data entries - lets call the function ValidateEntry. For my purposes valid data is any cell in the range whose value is either:- 1) Null (Blank or empty) 2) "X" (the letter X) 3) Begins with the letters "DC" and is immediately followed by 2 digits e.g DC09 4) Begins with the letters "SC" and is immediately followed by 2 digits e.g SC25 e.g ValidateEntry(A5:Z5) would return a "true" statues if all cells within that range met the criteria above, if not then it would return a "false" answer (I.e boolean argument). I want to then use this function in an If statement e.g If(ValidateEntry(A5:Z5),"Hooray!","Ooops!") Sorry - still learning!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to validate entries in a defined range
Cheers Bob,
It worked a treat. I just changed the Len function's to "= 4" to force a format of SC or DC with 2 digits e.g. SC01 ok but SC111 not ok (for my purposes all valid SC or DC entries would always be followd by 2 digits exactly). Thanks again. "Bob Phillips" wrote in message ... Function ValidateEntry(rng As Range) As Boolean Dim cell As Range ValidateEntry = True For Each cell In rng If (IsEmpty(cell.Value) Or _ cell.Value = "" Or _ cell.Value = "X" Or _ (Len(cell.Value) 3 And Left(cell.Value, 2) = "DC" And _ IsNumeric(Mid(cell.Value, 3, 1)) And IsNumeric(Mid(cell.Value, 4, 1))) Or _ (Len(cell.Value) 3 And Left(cell.Value, 2) = "SC" And _ IsNumeric(Mid(cell.Value, 3, 1)) And IsNumeric(Mid(cell.Value, 4, 1)))) Then Else ValidateEntry = False Exit Function End If Next cell End Function -- HTH RP (remove nothere from the email address if mailing direct) "Lee Wold" wrote in message ... I want to create a function that tests a range of cells stated to ensure that the cells contain only valid data entries - lets call the function ValidateEntry. For my purposes valid data is any cell in the range whose value is either:- 1) Null (Blank or empty) 2) "X" (the letter X) 3) Begins with the letters "DC" and is immediately followed by 2 digits e.g DC09 4) Begins with the letters "SC" and is immediately followed by 2 digits e.g SC25 e.g ValidateEntry(A5:Z5) would return a "true" statues if all cells within that range met the criteria above, if not then it would return a "false" answer (I.e boolean argument). I want to then use this function in an If statement e.g If(ValidateEntry(A5:Z5),"Hooray!","Ooops!") Sorry - still learning!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to validate entries in a defined range
Hi Lee,
I wondered about that. Glad you cracked it. Bob "Lee Wold" wrote in message ... Cheers Bob, It worked a treat. I just changed the Len function's to "= 4" to force a format of SC or DC with 2 digits e.g. SC01 ok but SC111 not ok (for my purposes all valid SC or DC entries would always be followd by 2 digits exactly). Thanks again. "Bob Phillips" wrote in message ... Function ValidateEntry(rng As Range) As Boolean Dim cell As Range ValidateEntry = True For Each cell In rng If (IsEmpty(cell.Value) Or _ cell.Value = "" Or _ cell.Value = "X" Or _ (Len(cell.Value) 3 And Left(cell.Value, 2) = "DC" And _ IsNumeric(Mid(cell.Value, 3, 1)) And IsNumeric(Mid(cell.Value, 4, 1))) Or _ (Len(cell.Value) 3 And Left(cell.Value, 2) = "SC" And _ IsNumeric(Mid(cell.Value, 3, 1)) And IsNumeric(Mid(cell.Value, 4, 1)))) Then Else ValidateEntry = False Exit Function End If Next cell End Function -- HTH RP (remove nothere from the email address if mailing direct) "Lee Wold" wrote in message ... I want to create a function that tests a range of cells stated to ensure that the cells contain only valid data entries - lets call the function ValidateEntry. For my purposes valid data is any cell in the range whose value is either:- 1) Null (Blank or empty) 2) "X" (the letter X) 3) Begins with the letters "DC" and is immediately followed by 2 digits e.g DC09 4) Begins with the letters "SC" and is immediately followed by 2 digits e.g SC25 e.g ValidateEntry(A5:Z5) would return a "true" statues if all cells within that range met the criteria above, if not then it would return a "false" answer (I.e boolean argument). I want to then use this function in an If statement e.g If(ValidateEntry(A5:Z5),"Hooray!","Ooops!") Sorry - still learning!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a row within a range that is defined in a sum function | Excel Discussion (Misc queries) | |||
validate time entries linked to each other | Excel Worksheet Functions | |||
Possible to validate entries not to contain 00 | Excel Discussion (Misc queries) | |||
How to validate entries with Data Forms in xls 2003? | Setting up and Configuration of Excel | |||
Validate entries | Excel Programming |