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

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

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

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

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