VBA Code to Extract complete row if the criteria did not match with 4 columns

Hello again,

I have tring to fect a solution on the net from few weeks but fail to get it. That is why i am posting my problem here in hope someone will help me.

I am always receiving a big list which is containing incorrect information usually. So, I want a help from this site experts to create a VBA Macros which will be named as ERROR LOG FILE. The script should generate a new workbook for the entire error records and which will highlight cell for wrong entry for a particular record.

In column B only M or F letters are allowed. If more than 1 letter i.e. Space, Special character or any value which is not match to these 2 letters i.e. M or F then copy complete row to a new workbook with remarks will be added in column G & should say “Only 1 letter (M or F) is allowed as gender”.

In column C only VIP, A, B & C is allowed. If any character which is not matching to these 4 items then copy complete row to a new workbook with remarks will be added in column G & should say “Only VIP, A, B & C is allowed in class”.

In column D only 3 numeric values i.e. 0 to 999 is allowed but no spaces. If more than 3 numeric value then copy complete row to a new workbook with remarks will be added in column G & should say “Only 3 characters or numeric values are allowed in Age”.

In column E only 3 words is allowed i.e. Employee, Spouse & Child. If any value in this column which doesn’t match with these 3 words then copy complete row to a new workbook with remarks will be added in column G & should say “Employee, Spouse or child is allowed in this column.

In column F only 5 words is allowed i.e. African, Asian, Middle East, Saudi & Other. If any value in column F which doesn’t match with these 5 words then copy complete row to a new workbook with remarks will be added in column G & should say “African, Asian, Middle East, Saudi & Other is allowed in this column.

Now the conditions. If the above mentioned 5 points have any error in any of the columns then it will copy complete row of error record to a newly generated workbook with remarks as mentioned above. Spaces, special character, spaces are not allowed i.e. only exactly match values are allowed otherwise the row will assume as error in record and copies to error log file which will be generated by your script.


Copy of original list, sample of accepting output is attached for your refference.

THis isn't that hard to put into a simple formula, then you can filter your data right there on the original sheet, no macro, no fuss.

On your main sheet, put this formula into G2, then copy down far enough to cover the dataset:

=IF(AND(B2<>"M",B2<>"F"), "Only 1 letter (M or F) is allowed as gender",
IF(NOT(OR(C2="VIP",C2="A",C2="B",C2="C")), "Only VIP, A, B & C is allowed in class",
IF(NOT(AND(ISNUMBER(D2), D2>=0, D2<=999)), "Only 3 characters or numeric values are allowed in Age",
IF(NOT(ISNUMBER(SEARCH(E2, "EMPLOYEESPOUSECHILD"))), "Employee, Spouse or child is allowed in this column",
IF(NOT(ISNUMBER(SEARCH(F2, "African, Asian, Middle East, Saudi, Other"))), "African, Asian, Middle East, Saudi & Other is allowed in this column", "")))))


Now apply a filter to column G to show all non-blanks and voila! Make your corrections, turn off the filter and copy data to permanent file. Just reuse this sheet, drop your new data in and bingo.

I am always receiving a big list which is containing incorrect information usually.

You might be able to better the sheet issued for the "big list" with data validation.

See this workbook try filling with something incorrect.

No fuss no errors no time wasted ...

OK, you don't want to test those things sequentially, you want to test them ALL every time and display ALL errors.

Format column G to Text Wrap, then put this in G2, copy down:

[B=IF(OR(B2<>"M",B2<>"F"), "", "Only 1 letter (M or F) is allowed as gender" & CHAR(10)) &
IF(OR(C2="VIP",C2="A",C2="B",C2="C"), "", "Only VIP, A, B & C is allowed in class" & CHAR(10)) &
IF(AND(ISNUMBER(D2), D2>=0, D2<=999), "", "Only 1-3 character numeric values are allowed in Age" & CHAR(10)) &
IF(ISNUMBER(SEARCH(E2, "EMPLOYEESPOUSECHILD")), "", "Employee, Spouse or child is allowed in this column" & CHAR(10)) &
IF(AND(LEN(TRIM(F2))>0, ISNUMBER(SEARCH(F2, "African, Asian, Middle East, Saudi, Other"))), "", "African, Asian, Middle East, Saudi & Other is allowed in this column")[/B]

No, follow my instruction and turn on the WRAP taxt function for those cells. Or replace the & CHAR(10) & with & "," &