Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any sample code to verify data?
I have an input sheet which received the data from someone else.
All the columns have some restriction, such as one date field must be greater than another date field, etc. I need to check the data integrity based on the data rules I have for each column. If the data is not satisfied, it will pop up some warning messages, or highlight the cells. Are there any sample codes that I can borrow? Appreciate your help greatly, Ron |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any sample code to verify data?
Given the number of formats people may want to input dates, you will make
your own life more easy if you provide them with a calendar control. This should get you started : http://www.google.co.uk/search?hl=en...Se arch&meta= NickHK "ron" wrote in message oups.com... I have an input sheet which received the data from someone else. All the columns have some restriction, such as one date field must be greater than another date field, etc. I need to check the data integrity based on the data rules I have for each column. If the data is not satisfied, it will pop up some warning messages, or highlight the cells. Are there any sample codes that I can borrow? Appreciate your help greatly, Ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any sample code to verify data?
Nick, thanks for the reponse. It is not just about date. The issue is,
I will receive a sheet and need to verify that all the fields, such as: one date field needs to be greater than another date field One text field must be value of "Inv" or blank, but nothing else One numerical field must be positive number, etc. I need to make sure the sheet I received satisfies all the conditions. Otherwise it will highlight the cells which do not. thanks, Ron NickHK wrote: Given the number of formats people may want to input dates, you will make your own life more easy if you provide them with a calendar control. This should get you started : http://www.google.co.uk/search?hl=en...Se arch&meta= NickHK "ron" wrote in message oups.com... I have an input sheet which received the data from someone else. All the columns have some restriction, such as one date field must be greater than another date field, etc. I need to check the data integrity based on the data rules I have for each column. If the data is not satisfied, it will pop up some warning messages, or highlight the cells. Are there any sample codes that I can borrow? Appreciate your help greatly, Ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any sample code to verify data?
Sorry, I misread "data" as date in the subject.
You could set up DataValidation rules on the worksheet. Or if you wish to use a class module, you would have easier maintenance in the events that the criteria change; you would have to change only one place. A brief example below. You could pass the whole range to the class instead (or an array of values), improve the criteria detection, input checking that a single cell is passed, include more properties so it can be configured better, return an error string or Raise an error upon failure... Depends how complex you wish to get. NickHK <cDataValidation Private Const AllowedEntries As String = "Inv, " Public Function ValidateData(StartCell As Range) As Boolean 'Or String Or Long ValidateData = False With StartCell If .Value .Offset(0, 1).Value Then 'Is Date greater the next cell ? If InStr(.Offset(0, 2).Value, AllowedEntries) 0 Then 'Is Inv or blank ? If .Offset(0, 3).Value = 0 Then 'Is +ve ? ValidateData = True End If End If End If End With End Function </cDataValidation <Worksheet Private Sub cmdValidate_Click() Dim cell As Range Dim MyValidation As cDataValidation Set MyValidation = New cDataValidation For Each cell In Range("A1:A100") With MyValidation If .ValidateData(cell) = False Then MsgBox "Error in row : " & cell.Address End With Next End Sub </Worksheet "ron" wrote in message oups.com... Nick, thanks for the reponse. It is not just about date. The issue is, I will receive a sheet and need to verify that all the fields, such as: one date field needs to be greater than another date field One text field must be value of "Inv" or blank, but nothing else One numerical field must be positive number, etc. I need to make sure the sheet I received satisfies all the conditions. Otherwise it will highlight the cells which do not. thanks, Ron NickHK wrote: Given the number of formats people may want to input dates, you will make your own life more easy if you provide them with a calendar control. This should get you started : http://www.google.co.uk/search?hl=en...Se arch&meta= NickHK "ron" wrote in message oups.com... I have an input sheet which received the data from someone else. All the columns have some restriction, such as one date field must be greater than another date field, etc. I need to check the data integrity based on the data rules I have for each column. If the data is not satisfied, it will pop up some warning messages, or highlight the cells. Are there any sample codes that I can borrow? Appreciate your help greatly, Ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any sample code to verify data?
Thanks so much for your help. It is very informative.
NickHK wrote: Sorry, I misread "data" as date in the subject. You could set up DataValidation rules on the worksheet. Or if you wish to use a class module, you would have easier maintenance in the events that the criteria change; you would have to change only one place. A brief example below. You could pass the whole range to the class instead (or an array of values), improve the criteria detection, input checking that a single cell is passed, include more properties so it can be configured better, return an error string or Raise an error upon failure... Depends how complex you wish to get. NickHK <cDataValidation Private Const AllowedEntries As String = "Inv, " Public Function ValidateData(StartCell As Range) As Boolean 'Or String Or Long ValidateData = False With StartCell If .Value .Offset(0, 1).Value Then 'Is Date greater the next cell ? If InStr(.Offset(0, 2).Value, AllowedEntries) 0 Then 'Is Inv or blank ? If .Offset(0, 3).Value = 0 Then 'Is +ve ? ValidateData = True End If End If End If End With End Function </cDataValidation <Worksheet Private Sub cmdValidate_Click() Dim cell As Range Dim MyValidation As cDataValidation Set MyValidation = New cDataValidation For Each cell In Range("A1:A100") With MyValidation If .ValidateData(cell) = False Then MsgBox "Error in row : " & cell.Address End With Next End Sub </Worksheet "ron" wrote in message oups.com... Nick, thanks for the reponse. It is not just about date. The issue is, I will receive a sheet and need to verify that all the fields, such as: one date field needs to be greater than another date field One text field must be value of "Inv" or blank, but nothing else One numerical field must be positive number, etc. I need to make sure the sheet I received satisfies all the conditions. Otherwise it will highlight the cells which do not. thanks, Ron NickHK wrote: Given the number of formats people may want to input dates, you will make your own life more easy if you provide them with a calendar control. This should get you started : http://www.google.co.uk/search?hl=en...Se arch&meta= NickHK "ron" wrote in message oups.com... I have an input sheet which received the data from someone else. All the columns have some restriction, such as one date field must be greater than another date field, etc. I need to check the data integrity based on the data rules I have for each column. If the data is not satisfied, it will pop up some warning messages, or highlight the cells. Are there any sample codes that I can borrow? Appreciate your help greatly, Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sample code to be fixed! | Excel Discussion (Misc queries) | |||
do anybody have a sample code for executing excel macro from vb code?<eom | Excel Programming | |||
Need sample code | Excel Programming | |||
Registration Key Sample Code | Excel Programming | |||
Need Sample VBA Code | Excel Programming |