Macro to Validate existing data.

Hi,
I need a macro to check values in a sheet.

The values need to be consistent with the following rules:
1. Names consisting of the following characters: A-Z, a-z, 0-9, # _ - [ ] @ only.
2. The character _ cannot be used as the first or the last character of the ID
3. Consecutive use of the character _ that is __ is not allowed
4. The maximum size allowed is 21

The error values should be highlighted as shown in attachment, and There should also be a log in one of the sheets that outputs a statement that " the cluster value for Utrancell ABC is in error".


Thanks

Hello cbhawsar,

This macro has been tested on the workbook you posted. I believe it to correct but you should double check it. The macro below has been added to the attached workbook and can be run by clicking a button on the "Input" sheet. The errors are listed on the "Output" sheet.

Please Login or Register  to view this content.

@ 111StepsAhead - Not so difficult when you know how.

I spent most of the evening on this so I thought I'd post it anyway, despite Leith Ross having provided a solution some hours ago ;-)

My biggest problem was the Regular Expression coding ... hence I have plagiarised this from Leith. Thanks.

I'd also worked out a different test for the multiple underscores and I've replaced that with Leith's second RegExp test.

The code is a little different, reflecting styles more than anything. One key point is that I tag the log entries onto the end which you may or may not want.

Please Login or Register  to view this content.

I hope you will find the alternative approach gives you some ideas.

Regards

Hello TMShucks,

You may not have realized that the expression below does not allow the string to begin or end with an underscore.

Please Login or Register  to view this content.

The "OR" condition in Regular Expressions is denoted by the pipe character "|". Easy to miss in all the characters. The caret "^" outside the parenthesis marks the beginning of the string and the "$" marks the end of the string.

In between, the pattern looks for any characters that do not match: A-Z, a-z, 0-9, # _ - [ ] @
If the tested string returns a true value then there is a error.

@Leith Ross: thank you for the clarification. That had escaped me ;-)

I tend to look at the use of Regular Expressions with some awe. It's useful to have a practical example.

Thanks again

See my signature or the FAQ.

Regards