Hi
I have written macro to compare two sheets.

can any one help me to write macro -
if there is data validation error then delete the cell value (leave blank in
that cell)
so when i run this macro it should check for data validation and if there is
an error
say date field is entred incorrectly then it should delete that cell value
and leave the value blank.

Thanks a lot in advance!

Hi,

What am I missing? If you have data validation on your worksheet then how
can a validated cell contain incorrect data?

Mike

"kay" wrote:

Hi
I have written macro to compare two sheets.

can any one help me to write macro -
if there is data validation error then delete the cell value (leave blank in
that cell)
so when i run this macro it should check for data validation and if there is
an error
say date field is entred incorrectly then it should delete that cell value
and leave the value blank.

Thanks a lot in advance!

Unlike Access, which detects invalid data in existing data when data
validation is in place, Excel doesn't do that. If this person has
pre-existing data and then put data validation in place after the fact, it
will be in force for new data or when you want to change existing data that
already doesn't comply with the validation rules. I actually like that Excel
doesn't behave like Access in this instance so I can manually correct the
invalid data.
--
Please rate posts so we know when we have answered your questions. Thanks.


"Mike H" wrote:

Hi,

What am I missing? If you have data validation on your worksheet then how
can a validated cell contain incorrect data?

Mike

"kay" wrote:

Hi
I have written macro to compare two sheets.

can any one help me to write macro -
if there is data validation error then delete the cell value (leave blank in
that cell)
so when i run this macro it should check for data validation and if there is
an error
say date field is entred incorrectly then it should delete that cell value
and leave the value blank.

Thanks a lot in advance!

Give this a try...

Sub DeleteInvalidData()
Dim C As Range
For Each C In Selection
If C.Validation.Value = False Then C.Clear
Next
End Sub

where you would obviously select the cells you want to test before running
it.

--
Rick (MVP - Excel)


"Orion Cochrane" wrote in message
...
Unlike Access, which detects invalid data in existing data when data
validation is in place, Excel doesn't do that. If this person has
pre-existing data and then put data validation in place after the fact, it
will be in force for new data or when you want to change existing data
that
already doesn't comply with the validation rules. I actually like that
Excel
doesn't behave like Access in this instance so I can manually correct the
invalid data.
--
Please rate posts so we know when we have answered your questions. Thanks.


"Mike H" wrote:

Hi,

What am I missing? If you have data validation on your worksheet then how
can a validated cell contain incorrect data?

Mike

"kay" wrote:

Hi
I have written macro to compare two sheets.

can any one help me to write macro -
if there is data validation error then delete the cell value (leave
blank in
that cell)
so when i run this macro it should check for data validation and if
there is
an error
say date field is entred incorrectly then it should delete that cell
value
and leave the value blank.

Thanks a lot in advance!

Thanks for your quick response.

Actually what i am doing , i am comparing two sheets - sheet1 with sheet2
my input is in sheet1 and i update sheet2 by comparing sheet1.

i dont know some how it copies the value of sheet1 into sheet 2 even if
there is data validation.
For eg Phone number in sheet 2 is number format. ( Sheet 1 does not have
data validatation and dont want to put validation for that column for some
reason)
So incase if some one enters text in sheet 1 , it will copy that value and
add/update to sheet2 (when i run that compare macro).
so i want to check if there is invlalid data in the sheet 2 then delete that
data and leave cell value as blank.

Hope i explained well.
Thanks



"Mike H" wrote:

Hi,

What am I missing? If you have data validation on your worksheet then how
can a validated cell contain incorrect data?

Mike

"kay" wrote:

Hi
I have written macro to compare two sheets.

can any one help me to write macro -
if there is data validation error then delete the cell value (leave blank in
that cell)
so when i run this macro it should check for data validation and if there is
an error
say date field is entred incorrectly then it should delete that cell value
and leave the value blank.

Thanks a lot in advance!

If you are doing a straight copy-&-paste between Sheet1 (no validation) and
Sheet2 (with validation), you are copying Sheet1's properties as well as
contents. Therefore, Sheet2 will not have data validation if Sheet1 doesn't.
Try pasting as values instead so you can keep Sheet2's data validation.
<This is hopefully the part where someone who knows how to Paste Special in
VBA reads this and replies with how to Paste Values, as I do not know how to
do that
--
Please rate posts so we know when we have answered your questions. Thanks.


"kay" wrote:

Thanks for your quick response.

Actually what i am doing , i am comparing two sheets - sheet1 with sheet2
my input is in sheet1 and i update sheet2 by comparing sheet1.

i dont know some how it copies the value of sheet1 into sheet 2 even if
there is data validation.
For eg Phone number in sheet 2 is number format. ( Sheet 1 does not have
data validatation and dont want to put validation for that column for some
reason)
So incase if some one enters text in sheet 1 , it will copy that value and
add/update to sheet2 (when i run that compare macro).
so i want to check if there is invlalid data in the sheet 2 then delete that
data and leave cell value as blank.

Hope i explained well.
Thanks



"Mike H" wrote:

Hi,

What am I missing? If you have data validation on your worksheet then how
can a validated cell contain incorrect data?

Mike

"kay" wrote:

Hi
I have written macro to compare two sheets.

can any one help me to write macro -
if there is data validation error then delete the cell value (leave blank in
that cell)
so when i run this macro it should check for data validation and if there is
an error
say date field is entred incorrectly then it should delete that cell value
and leave the value blank.

Thanks a lot in advance!

Thanks
i am using this formula to update value from sheet 1
where it reades cell value from sheet 1 and compare with sheet2
if there is any change then update the value.
readcells3 < readcellsc3 Then
ActiveCell.Value = readcells3
so that works fine.

data validation error does not happen frequently but
sometimes it happens and thats why i need to run some kind of macro which
just delete the cell value. so to run another macro it wont give error .

appreciate your help.

"Orion Cochrane" wrote:

If you are doing a straight copy-&-paste between Sheet1 (no validation) and
Sheet2 (with validation), you are copying Sheet1's properties as well as
contents. Therefore, Sheet2 will not have data validation if Sheet1 doesn't.
Try pasting as values instead so you can keep Sheet2's data validation.
<This is hopefully the part where someone who knows how to Paste Special in
VBA reads this and replies with how to Paste Values, as I do not know how to
do that
--
Please rate posts so we know when we have answered your questions. Thanks.


"kay" wrote:

Thanks for your quick response.

Actually what i am doing , i am comparing two sheets - sheet1 with sheet2
my input is in sheet1 and i update sheet2 by comparing sheet1.

i dont know some how it copies the value of sheet1 into sheet 2 even if
there is data validation.
For eg Phone number in sheet 2 is number format. ( Sheet 1 does not have
data validatation and dont want to put validation for that column for some
reason)
So incase if some one enters text in sheet 1 , it will copy that value and
add/update to sheet2 (when i run that compare macro).
so i want to check if there is invlalid data in the sheet 2 then delete that
data and leave cell value as blank.

Hope i explained well.
Thanks



"Mike H" wrote:

Hi,

What am I missing? If you have data validation on your worksheet then how
can a validated cell contain incorrect data?

Mike

"kay" wrote:

Hi
I have written macro to compare two sheets.

can any one help me to write macro -
if there is data validation error then delete the cell value (leave blank in
that cell)
so when i run this macro it should check for data validation and if there is
an error
say date field is entred incorrectly then it should delete that cell value
and leave the value blank.

Thanks a lot in advance!

I'm just wondering if you were able to see my previous response in this
thread? I ask because I posted a macro which does what I think you appear to
still be asking for.

--
Rick (MVP - Excel)


"kay" wrote in message
...
Thanks
i am using this formula to update value from sheet 1
where it reades cell value from sheet 1 and compare with sheet2
if there is any change then update the value.
readcells3 < readcellsc3 Then
ActiveCell.Value = readcells3
so that works fine.

data validation error does not happen frequently but
sometimes it happens and thats why i need to run some kind of macro which
just delete the cell value. so to run another macro it wont give error .

appreciate your help.

"Orion Cochrane" wrote:

If you are doing a straight copy-&-paste between Sheet1 (no validation)
and
Sheet2 (with validation), you are copying Sheet1's properties as well as
contents. Therefore, Sheet2 will not have data validation if Sheet1
doesn't.
Try pasting as values instead so you can keep Sheet2's data validation.
<This is hopefully the part where someone who knows how to Paste Special
in
VBA reads this and replies with how to Paste Values, as I do not know how
to
do that
--
Please rate posts so we know when we have answered your questions.
Thanks.


"kay" wrote:

Thanks for your quick response.

Actually what i am doing , i am comparing two sheets - sheet1 with
sheet2
my input is in sheet1 and i update sheet2 by comparing sheet1.

i dont know some how it copies the value of sheet1 into sheet 2 even if
there is data validation.
For eg Phone number in sheet 2 is number format. ( Sheet 1 does not
have
data validatation and dont want to put validation for that column for
some
reason)
So incase if some one enters text in sheet 1 , it will copy that value
and
add/update to sheet2 (when i run that compare macro).
so i want to check if there is invlalid data in the sheet 2 then delete
that
data and leave cell value as blank.

Hope i explained well.
Thanks



"Mike H" wrote:

Hi,

What am I missing? If you have data validation on your worksheet then
how
can a validated cell contain incorrect data?

Mike

"kay" wrote:

Hi
I have written macro to compare two sheets.

can any one help me to write macro -
if there is data validation error then delete the cell value (leave
blank in
that cell)
so when i run this macro it should check for data validation and if
there is
an error
say date field is entred incorrectly then it should delete that
cell value
and leave the value blank.

Thanks a lot in advance!