Count invalid data entries

[plantechbl]

I am using the following macro to identify invalid data entries in
cells with data validation applied.

Sub CheckOrder()
' Application.CommandBars("Formula Auditing").Visible = True
ActiveSheet.CircleInvalid
Sheets("Configuration").CircleInvalid
Sheets("Parts_TakeOff").CircleInvalid
MsgBox ("Check for Red Circled Invalid Data Entries on" & Chr(10) &
" Configuration and Parts_TakeOff Sheets")
End Sub

Is there a way to count the number of invalid data entries (red
circles) and write the number to a particular cell

Thanks in advance,
Bill

 

[Don Guillett]

I've not used circleinvalid but try
for each c in selection
if c.circleinvalid=true then mc=mc+1
next
msgbox mc

 

[plantechbl]

Don:

Sub CheckOrder()
' Application.CommandBars("Formula Auditing").Visible = True
ActiveSheet.CircleInvalid
Sheets("Configuration").CircleInvalid
Sheets("Parts_TakeOff").CircleInvalid
For Each c In Selection
If c.CircleInvalid = True Then mc = mc + 1
Next
MsgBox mc
'MsgBox ("Check for Red Circled Invalid Data Entries on" & Chr(10)
& " Configuration and Parts_TakeOff Sheets")

End Sub

I may not have placed the code in the macro correctly. It is hanging
up on the line
If c.CircleInvalid = True Then mc = mc + 1

Thanks for the prompt reply,
Bill

 

[Don Guillett]

See if you can adapt this
http://groups.google.com/group/micr...f3fccf31173?lnk=st&q=&rnum=6#e5e7df3fccf31173

 

[Don Guillett]

if the long entry doesn't work

http://tinyurl.com/yk5oxz

 

[plantechbl]

Don,
Thanks for pointing me in the right direction. The following code
seems to be working fine. It counts the number of invalid entries
(circles) and places the number in cell A1. I can then use this value
to alert the user not only that they have invalid entries but also
count the number of invalid entries.

Thanks for help on this one! You have helped me out one other time
about a week ago and it is very much appreciated!
Bill

Sub CheckOrder1()
Dim DataRange As Range
Dim c As Range
Dim mycount As Integer

If Range("Summary_Report").SpecialCells(xlCellTypeAllValidation).count
<> 0 Then
GoTo 1:
Else
'Application.EnableEvents = True
Exit Sub
End If
1:
'Application.EnableEvents = False
Set DataRange = Range("Summary_Report")
mycount = 0

ActiveSheet.CircleInvalid

For Each c In DataRange
If Not c.Validation.Value Then
mycount = mycount + 1
End If
Next

If mycount = 0 Then Range("A1").Value = "0"
If mycount <> 0 Then

Range("A1").Value = mycount
End If
Application.EnableEvents = True
Exit Sub

End Sub

 

[Don Guillett]

Glad to help.