Count invalid data entries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

plantechbl@earthlink.net - 21 Dec 2006 15:37 GMT

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

Reply to this Message

Don Guillett - 21 Dec 2006 16:38 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>I am using the following macro to identify invalid data entries in
> cells with data validation applied.
[quoted text clipped - 13 lines]
> Thanks in advance,
> Bill

Reply to this Message

plantechbl@earthlink.net - 21 Dec 2006 17:04 GMT

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

> I've not used circleinvalid but try
> for each c in selection
[quoted text clipped - 23 lines]
> > Thanks in advance,
> > Bill

Reply to this Message

Don Guillett - 21 Dec 2006 17:35 GMT

See if you can adapt this
http://groups.google.com/group/microsoft.public.excel/browse_frm/thread/58bedd18
a5736c83/e5e7df3fccf31173?lnk=st&q=&rnum=6#e5e7df3fccf31173

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Don:
>
[quoted text clipped - 46 lines]
>> > Thanks in advance,
>> > Bill

Reply to this Message

Don Guillett - 21 Dec 2006 17:36 GMT

if the long entry doesn't work

http://tinyurl.com/yk5oxz

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> I've not used circleinvalid but try
> for each c in selection
[quoted text clipped - 19 lines]
>> Thanks in advance,
>> Bill

Reply to this Message

plantechbl@earthlink.net - 21 Dec 2006 21:04 GMT

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

> if the long entry doesn't work
>
[quoted text clipped - 31 lines]
> >> Thanks in advance,
> >> Bill

Reply to this Message

Don Guillett - 21 Dec 2006 21:32 GMT

Glad to help.

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Don,
> Thanks for pointing me in the right direction. The following code
[quoted text clipped - 77 lines]
>> >> Thanks in advance,
>> >> Bill

Reply to this Message