Data Validation of Pasted Value

**Bingo123** · 05-20-2010, 03:49 AM

Hi,
I have a problem when i pasted over a value from another worksheet to the worksheet which has data validation appplied on all the cells.

For example, I want to copy the values from 'Sheet 3' to 'Sheet 1'.
Sheet 1 has the data validation with the condition such that a text length which doesn't have 9 digits will prompt an error message ' Pls enter 9 digits'.

There is a similar macro which i think can be used for this too. But im not too sure where i should edit to change to the condition that i want.

Please Login or Register  to view this content.

When i try using the data validation on sheet 1 and do a normal data entry it works fine. Just have a problem with copying and pasting part. Any help will be much appreciated. Thanks.

**teylyn** · 05-20-2010, 04:59 AM

Bingo,

data validation works on manually entered values only. If you paste values via code, then you need to create code to check if the pasted value meets the criteria before pasting it.

**Nett** · 05-20-2010, 09:38 PM

Thanks teylyn for your reply.=) There are 2 ways that I can solve this. Either by formula or macro. But i really badly need help in the editing part.

I have this formula that I can add to column C that returns a Y/N result if the text length of a value in column A is 9, it will return yes. if value is lesser or more than 9 than returns no.

=IF(OR(LEFT(A1,2),LEFT(A1,2)),IF(LEN(B1)>9,"y","n"),"")

I have this macro that reads every cell in the worksheet and deletes rows if the text length is lesser or more than 9. But when i run it it gives me a compile error. I think the problem is small but i can't put my finger as to where the problem lies. Help? Anyone?

Please Login or Register  to view this content.

**Nett** · 05-20-2010, 09:42 PM

Bingo123, you can use the above codes but some editing should be done for both of the formulas and macro. That's the best that I can do to help. Cheers!