Imported data does not follow data validation rules

[wpryan]

Hi there...I have a worksheet that has some cells that are supposed to be checked by data validation rules (whole number, between 960 and 1040). If one tries to enter values outside the range, the data validation works. However, I have some data imported from a text file (via vba) that go into those cells, and if the values are outside the range, the data is imported without error. Is this something that can be checked by vba?

 

[Peter_SSs]

Is this something that can be checked by vba?

Most likely.
Can you post the current vba that imports the data? (My signature block below has info about how to best post vba code)

What exactly do you want to happen if some of the data fails the data validation test?

 

[wpryan]

Most likely.
Can you post the current vba that imports the data? (My signature block below has info about how to best post vba code)

What exactly do you want to happen if some of the data fails the data validation test

Most likely.
Can you post the current vba that imports the data? (My signature block below has info about how to best post vba code)

What exactly do you want to happen if some of the data fails the data validation test?

Hello, thanks for your reply. the relevant code is:

'Copy Park position
                    wsImport.Range("C3:C39").Copy
                    wsData.Range("C10:C46").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                    wsData.Range("C10:C46").NumberFormat = "0"
                   
                'Park position Rotator Find Position
                   wsImport.Range("B41").Copy
                   wsData.Range("D5").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                   
                    'Copy OS position
                    wsImport.Range("C43:C79").Copy
                    wsData.Range("G10:G46").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                    wsData.Range("G10:G46").NumberFormat = "0"

                'OS position Rotator Find Position
                    wsImport.Range("B81").Copy
                    wsData.Range("H5").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                                      
                'Copy OD position
                    wsImport.Range("C83:C119").Copy
                    wsData.Range("K10:K46").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                    wsData.Range("K10:K46").NumberFormat = "0"

What I kind of expected to see was the normal message one would see if the data validation criteria is not met:

 

[Peter_SSs]

Thanks for the code.

What I kind of expected to see was the normal message one would see if the data validation criteria is not met:

There are a couple of issues involved

• You are entering several values at once, some of which may be valid and some of which may be invalid.
• The issue is not specifically related to vba. If you manually copy an entry from elsewhere and paste special - values into a data validated cell the message does not pop up even if that pasted value does not meet the DV criteria.

You could try the following. It should put a red oval around any invalid entries pasted in. Would that be sufficient?
If not I think you would need code to loop through all the cells in those pasted ranges and check each one individually. If you decide that you need that, you would need to explain exactly what you want to happen if, for example, C13, C17, C20:C25, H5 and K40:K42 were invalid and everything else was valid.

wsData.ClearCircles

' Put all of your copy/pastespecial code here

wsData.CircleInvalid