VBA code to count invalid Values

Posts
13
Registration date
Thursday May 9, 2019
Status
Member
Last seen
May 28, 2019
-
Hi,

I have created a vba code to circle the invalid data based on the custom data validation rule being written.

I want to count the all those invalid data being circled, on running my macro to check the validation.

How to count the invalid red circle marked on data cells

Plz help me.


Thanks for your time.
See more 

4 replies

Best answer
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1572
1
Thank you
What makes the data invalid? Use countif to qualify the count using the same rules you used to mark the data invalid.

So lets say any value less than 0 is invalid. The countif would be:

=countif (A1:A10, <0)


Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 5678 users have said thank you to us this month

Sanju_7454
Posts
13
Registration date
Thursday May 9, 2019
Status
Member
Last seen
May 28, 2019
3 -
I tried this before posting the query.

I have a column called Price. The column can allow only Numeric. THis is being done in Excel validation and it works when the user is entering the value manually.

suppose, user copy paste the data, the validation is over-ruled and column takes those value which is wrong. In order to validate, I am running macro at form level, where all the cell data is validated.

Now i want to show the count of empty cells and the number of invalid data being entered in the column

Plz help me on this
Respond to ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1572
1
Thank you
Ok, so in the form validator script, make a variable and increment the variable each time it finds a value that is invalid, if countif, counta, or count wont work.

I am confused by your work flow, as you state that the form validator is validating the cell value, but in the end you want to know the number of blanks and invalids. The blanks can be trapped by count, and the form validator knows if the value is good or not. So take the count of invalids the form validator finds, and add it to the count value of the blanks!!!! It is probably best to just have the validator look for blanks, too.

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 5678 users have said thank you to us this month

Sanju_7454
Posts
13
Registration date
Thursday May 9, 2019
Status
Member
Last seen
May 28, 2019
3 -
Thank u very much Sir
Respond to ac3mark
Posts
13
Registration date
Thursday May 9, 2019
Status
Member
Last seen
May 28, 2019
3
0
Thank you
I tried this before posting the query.

I have a column called Price. The column can allow only Numeric. THis is being done in Excel validation and it works when the user is entering the value manually.

suppose, user copy paste the data, the validation is over-ruled and column takes those value which is wrong. In order to validate, I am running macro at form level, where all the cell data is validated.

Now i want to show the count of empty cells and the number of invalid data being entered in the column

Plz help me
Respond to Sanju_7454
Posts
13
Registration date
Thursday May 9, 2019
Status
Member
Last seen
May 28, 2019
3
0
Thank you
Hi..

Thank u very much for your valuable time and help. I fixed the above problem.

I have a new query. That is, i waant to validate the date and time in the below fashion

mm/dd/yyyy h:mm (Eg: 05/20/2019 5:10 or 05/20/2019 23:30)

Excel should not allow user to miss any of the character format.

I tried this =AND(ISNUMBER(A1),LEFT(CELL("format",A1),1)="D4") .
But it is allowing the format m/d/yy or m/d/yy h:mm or mm/dd/yy


Thanks for your time.
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1572 -
Change the column to what ever format you want. then if the user paste in some value, it converts it to the proper format, or.....

add in another column to do the conversion, and check against that column....as in:

=TEXT(A1,"MM/DD/YYYY HH:MM")


So, if your date and time is originally pasted, or entered into A1, add in another column in lets say Z1, and evaluate that value based on the above format. So your test would be against Z1, not A1, because Z1 would be the correct format regardless of what was entered into A1.
Respond to Sanju_7454