Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ID Validation using macros in excel
Hi All,
I need help in creating a macro which can validate IDs listed in a column in spread sheet. There is a fixed format for each ID: The first 5 places of the ID should be characters. The next 4 should be numericals Last 1 should be a character 4th character from beginning should be "P" Examples of some of the IDs: ACCPR4243A BDKPZ6145M LNHPC3807D thank you all in advance. Sri... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ID Validation using macros in excel
Design a formula in XL that evaluates to TRUE/FALSE for
correct/incorrect ID's Use Data-validation and use option Formula. and use the created formula to evaluate ID's on cell data entry. Dm Unseen |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ID Validation using macros in excel
The function you;re looking for is TypeName.
So something like this might just work. ----------------------------------------------- Sub YourSubName() Dim strCellValue As String strCellValue = Range("A1").Text If TypeName(Left(strCellValue, 5)) = "String" And TypeName(Mid(strCellValue, 6, 4)) = "Double" _ And TypeName(Right(strCellValue, 1)) = "String" And Mid(strCellValue, 4, 1) = "p" Then Do Something Else MsgBox "Not correct format" End If End Sub ------------------------------------------------------------ Not sure if you want to loop through all of them in one hit or what you want to do if it's wrong, but this should be a good starter for 10 I hope. HTH. "Sridhar Machina" wrote: Hi All, I need help in creating a macro which can validate IDs listed in a column in spread sheet. There is a fixed format for each ID: The first 5 places of the ID should be characters. The next 4 should be numericals Last 1 should be a character 4th character from beginning should be "P" Examples of some of the IDs: ACCPR4243A BDKPZ6145M LNHPC3807D thank you all in advance. Sri... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ID Validation using macros in excel
Hi, Sridhar,
I couldn't get DaveO's solution to work, so i came up with this: Sub ValidateString() 'Characters 1-5 are non-numeric 'Character 5 = "P" 'Characters 6-9 are numbers 'Last Character is non-numeric Dim StrCellValue As String Dim TestCell As Range For Each TestCell In Selection StrCellValue = TestCell.Text If Not (IsNumeric(Left(StrCellValue, 5))) _ And Mid(StrCellValue, 4, 1) = "P" _ And IsNumeric(Mid(StrCellValue, 6, 4)) _ And Not (IsNumeric(Right(StrCellValue, 1))) Then MsgBox ("Value in row " & TestCell.Row & " - Format OK") Else MsgBox ("Value in row " & TestCell.Row & " - Format Incorrect") End If Next End Sub The macro works for a range of cells, as against just one - highlight all the cells you want to test before you run this macro - it should be OK Regards Pete "Sridhar Machina" wrote: Hi All, I need help in creating a macro which can validate IDs listed in a column in spread sheet. There is a fixed format for each ID: The first 5 places of the ID should be characters. The next 4 should be numericals Last 1 should be a character 4th character from beginning should be "P" Examples of some of the IDs: ACCPR4243A BDKPZ6145M LNHPC3807D thank you all in advance. Sri... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ID Validation using macros in excel
Thank you, peter, its working for me.
Thanks again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007-programming macros that apply to data validation | New Users to Excel | |||
Macros & Validation | New Users to Excel | |||
Validation/Macros | Excel Programming | |||
Validation/Macros | Excel Programming | |||
Validation/Macros | Excel Programming |