Function to validate entries in a defined range

[Lee Wold]

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!!!

 

[Bob Phillips]

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]

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]

Hi Lee,

I wondered about that. Glad you cracked it.

Bob

 

[Tushar Mehta]

Bob and you have already solved your specific problem, but you might
want to consider the following.

Since the validation function works across a range of cells, it would
be helpful to indicate which cell(s) in the range are unacceptable.
Towards that end, consider using XL's data validation or conditional
formatting capability. If you must use a UDF in the manner discussed
so far, have it return the cells in error. Yes, that means it cannot
return a boolean, but the result would be far more useful to your
user/customer.

Option Explicit

Function ValidateEntry(rng As Range) As String
Dim Cell As Range
For Each Cell In rng
If IsEmpty(Cell.Value) Or _
Cell.Value = "" Or _
Cell.Value = "X" Or _
(Len(Cell.Value) = 4 _
And (UCase(Left(Cell.Value, 2)) = "DC" _
Or UCase(Left(Cell.Value, 2)) = "SC") _
And IsNumeric(Mid(Cell.Value, 3, 1)) _
And IsNumeric(Mid(Cell.Value, 4, 1))) Then
Else
ValidateEntry = ValidateEntry & Cell.Address & ","
End If
Next Cell
If ValidateEntry = "" Then
ValidateEntry = "OK"
Else
ValidateEntry = "Errors in " _
& Left(ValidateEntry, Len(ValidateEntry) - 1)
End If
End Function

Also, note the consolidation of the DC and SC checks into a subordinate
OR clause and the use of UCase to support user entry in lower or upper
case letters.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions