cell validation/format masks???
 

Hi Everybody

I have a list of cells with alphanumeric data in it eg ("01R1234567")
I want to write an if statement to check that the contents of the cells is a
valid reference.

Is there any way to check the individual characters for there values
eg IF A1 = "NNANNNNNNN" Then B1 = "valid" (N=numberic A=Aplph)
else B1 = "corrupt"
End If

Thanks
Anthony

cell validation/format masks???
 

Hi Anthony,
I don't think there is any direct way. However you can use the worksheet
function bellow (HasPattern(,)) which uses the LIKE operator (see vba online
help)
'-------------------------------------------------------------------------------
'HasPattern returns the value of the expression: Str LIKE Pattern
'It returns #Value error if either STr or Pattern cannot be converted into
strings.
Public Function HasPattern(Str As Variant, Pattern As String) As Variant
Dim s1 As String, s2 As String

On Error Resume Next
s1 = CStr(Str)
s2 = CStr(Pattern)
If Err < 0 Then 'if error, returns #Value
HasPattern = CVErr(xlErrValue)
Else
HasPattern = (Str Like Pattern)
End If
End Function
'-----------------------------------------------------------------

In the sheet, in your case, in B1, enter:
=HasPattern(UPPER(A1),"##[A-Z]#######")
will retun True if A1 is like "NNANNNNNNN"
else False

You can also use it in :
-Conditional Formatting (Format of the cell varies based on a condition:
menu Format Conditional Formatting) or
- Dat Validation: validates user data entry (menu Data Validation)
- directly within vba

I hope this helps,
Sebastien

"antinoz" wrote:

Hi Everybody

I have a list of cells with alphanumeric data in it eg ("01R1234567")
I want to write an if statement to check that the contents of the cells is a
valid reference.

Is there any way to check the individual characters for there values
eg IF A1 = "NNANNNNNNN" Then B1 = "valid" (N=numberic A=Aplph)
else B1 = "corrupt"
End If

Thanks
Anthony

cell validation/format masks???
 

Didn't you see the several fine responses to your post of 9/8/2004 9:53 PM
??

"antinoz" wrote in message
...
Hi Everybody

I have a list of cells with alphanumeric data in it eg ("01R1234567")
I want to write an if statement to check that the contents of the cells is
a
valid reference.

Is there any way to check the individual characters for there values
eg IF A1 = "NNANNNNNNN" Then B1 = "valid" (N=numberic A=Aplph)
else B1 = "corrupt"
End If

Thanks
Anthony

cell validation/format masks???
 

A big thank you to all the guys who responded to the previous "duplication".
I foolishly lost the question and could not find it again so thinking I had
not infact posted one I did it again. apologies and thanks

thanks also sebastienm for responding along with, Bob, Don & Dave

cheers guys

"Bob Kilmer" wrote:

Didn't you see the several fine responses to your post of 9/8/2004 9:53 PM
??

"antinoz" wrote in message
...
Hi Everybody

I have a list of cells with alphanumeric data in it eg ("01R1234567")
I want to write an if statement to check that the contents of the cells is
a
valid reference.

Is there any way to check the individual characters for there values
eg IF A1 = "NNANNNNNNN" Then B1 = "valid" (N=numberic A=Aplph)
else B1 = "corrupt"
End If

Thanks
Anthony