Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate an account number
Given a general ledger account entered into a cell on the spreadsheet that
looks like: 123.123.1234.123456.123.12.123 Using VBA, I need to be sure it only contains either numbers or periods and no other stray characters (`,#$!-_=+ A-z ... etc), including blank spaces. What is the best way to test for this? Please note that I am already counting the periods to ensure the number of segments and checking the length of each segment... Thanks much in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate an account number
Sub account()
Dim s As String s = "123.123.1234.123456.123.12.123" b = IsNumeric(Replace(s, ".", "")) MsgBox (b) End Sub -- Gary''s Student - gsnu200736 "XP" wrote: Given a general ledger account entered into a cell on the spreadsheet that looks like: 123.123.1234.123456.123.12.123 Using VBA, I need to be sure it only contains either numbers or periods and no other stray characters (`,#$!-_=+ A-z ... etc), including blank spaces. What is the best way to test for this? Please note that I am already counting the periods to ensure the number of segments and checking the length of each segment... Thanks much in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate an account number
On Tue, 7 Aug 2007 06:26:03 -0700, XP wrote:
Given a general ledger account entered into a cell on the spreadsheet that looks like: 123.123.1234.123456.123.12.123 Using VBA, I need to be sure it only contains either numbers or periods and no other stray characters (`,#$!-_=+ A-z ... etc), including blank spaces. What is the best way to test for this? Please note that I am already counting the periods to ensure the number of segments and checking the length of each segment... Thanks much in advance. To merely check that the account contains only digits and periods, you could use this: ========================== Option Explicit Sub ChkNum() Dim c As Range Dim oRegex As Object Const sPattern As String = "[^0-9\.]+" Set oRegex = CreateObject("VBScript.Regexp") oRegex.Pattern = sPattern For Each c In Selection If oRegex.test(c.Text) = True Or _ Len(c.Text) = 0 Then Debug.Print c.Text, "Invalid" Else Debug.Print c.Text, "Valid" End If Next c End Sub ============================= If you wanted to check for a valid account number, how to do it would depend on the rules for a valid account number. But it would merely involve changing sPattern in the above. For example, if a valid account number were one formatted as you show, and had no leading or trailing <spaces, then you could set sPattern = "^\d{3}\.\d{3}\.\d{4}\.\d{6}\.\d{3}\.\d{2}\.\d{3}$ " Of course, you'd also have to reverse the "True/False" test, so: ================================================== ====== 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 ================================================ If there were more acceptable variability in Account Number, that could probably be included in sPattern also. --ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate an account number
On Tue, 7 Aug 2007 07:14:09 -0700, Gary''s Student
wrote: Sub account() Dim s As String s = "123.123.1234.123456.123.12.123" b = IsNumeric(Replace(s, ".", "")) MsgBox (b) End Sub Your routine does not filter out all extraneous characters, as specified by the OP. For example: s = "+123.123.1234.123456.123.12.123" s = "-123.123.1234.123456.123.12.123" s = "123.123.1234.123456.123.12.123-" s = "123.123.1234.123456.123.12.123+" s = "(123.123.1234.123456.123.12.123)" will all return TRUE, even though they include characters the OP specifically listed to be excluded. --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate an account number
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate an account number
On Tue, 7 Aug 2007 12:28:27 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: 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 That's true. I was thinking along the lines that if there is some variability in the numbers of digits allowed between each 'dot', then constructing a regex might be simpler. --ron |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 That's true. I was thinking along the lines that if there is some variability in the numbers of digits allowed between each 'dot', then constructing a regex might be simpler. If the OP needed flexibility in setting the pattern, I would probably add a Module to the VBA project and use a function something like this... Function CHECKNUM(Cell As Range, ParamArray FieldCount()) Dim X As Long Dim sPattern As String If Cell.Count < 1 Then CHECKNUM = "#CELL REF ERROR!" Else For X = LBound(FieldCount) To UBound(FieldCount) sPattern = sPattern & String$(FieldCount(X), "#") If X < UBound(FieldCount) Then sPattern = sPattern & "." Next CHECKNUM = Cell.Text Like sPattern End If End Function That way, the user could specify how many number go in each section and test that against a specified cell value. For example, if the value being checked were in A1 and the pattern was as originally posted, then the formula to use on the spreadsheet would be... =CHECKNUM(A1,3,3,4,6,3,2,3) If the value in, say, A2 needed to be checked against a pattern that looked like this 12.123.1234, then this would be the formula to use... =CHECKNUM(A2,2,3,4) I wasn't sure of the best way to report back an error if the specified range that wasn't a single cell, so I made up what I show in the code. Rick |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate an account number
On Tue, 7 Aug 2007 13:39:38 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: That way, the user could specify how many number go in each section and test that against a specified cell value. For example, if the value being checked were in A1 and the pattern was as originally posted, then the formula to use on the spreadsheet would be... =CHECKNUM(A1,3,3,4,6,3,2,3) If the value in, say, A2 needed to be checked against a pattern that looked like this 12.123.1234, then this would be the formula to use... =CHECKNUM(A2,2,3,4) I wasn't sure of the best way to report back an error if the specified range that wasn't a single cell, so I made up what I show in the code. There are many ways of solving a problem. But since we may not have all the parameters, it is difficult to argue with any particular approach. I was especially thinking of allowing a variable number of digits in each section. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mask Account Number | Excel Discussion (Misc queries) | |||
Insert Name based on the account number | Excel Discussion (Misc queries) | |||
How account duplicated number if condition is met? | Excel Discussion (Misc queries) | |||
Validate values for Whole Number only | Excel Programming | |||
validate phone number | Excel Programming |