Validate an account number
If sPattern is well defined (as shown in the example), you don't need to use
a Regular Expression to test it... VBA's Like operator is enough...
Sub ChkNum()
Dim c As Range
Dim oRegex As Object
Const sPattern As String = "###.###.####.######.###.##.###"
For Each c In Selection
If c.Text Like sPattern Then
Debug.Print c.Text, "Valid"
Else
Debug.Print c.Text, "Invalid"
End If
Next c
End Sub
Rick
"Ron Rosenfeld" wrote in message
...
On Tue, 07 Aug 2007 10:16:16 -0400, Ron Rosenfeld
wrote:
================================================ ========
Option Explicit
Sub ChkNum()
Dim c As Range
Dim oRegex As Object
Dim mcMatchCollection As Object
Const sPattern As String =
"^\d{3}\.\d{3}\.\d{4}\.\d{6}\.\d{3}\.\d{2}\.\d{3 }$"
Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Pattern = sPattern
For Each c In Selection
If oRegex.test(c.Text) = False Then
Debug.Print c.Text, "Invalid"
Else
Debug.Print c.Text, "Valid"
End If
Next c
End Sub
============================================== ==
Removing an extraneous line:
================================================== ======
Option Explicit
Sub ChkNum()
Dim c As Range
Dim oRegex As Object
Const sPattern As String =
"^\d{3}\.\d{3}\.\d{4}\.\d{6}\.\d{3}\.\d{2}\.\d{3}$ "
Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Pattern = sPattern
For Each c In Selection
If oRegex.test(c.Text) = False Then
Debug.Print c.Text, "Invalid"
Else
Debug.Print c.Text, "Valid"
End If
Next c
End Sub
================================================
--ron
|