delete cell value if there is data validation error

[kay]

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!

 

[Mike H]

Hi,

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

Mike

 

[Orion Cochrane]

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.

 

[kay]

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

 

[Rick Rothstein]

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.

 

[Orion Cochrane]

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>

 

[kay]

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.

 

[Rick Rothstein]

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.

 

[kay]

thanks
thats what i want,
sorry i just get stuck in too many things . when i saw your message some how
i did not see that part.
thanks a lot again,
i have just quick question if i dont want to select the range and look for
that in whole sheet2 then colud you please help me to wirte that statement,

 

[Rick Rothstein]

Change the Selection to UsedRange....

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

 

[kay]

Thanks Rick , this works , appreciate your help

Change the Selection to UsedRange....

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