Count invalid cells?

[Ajay Askoolum]

Data + Validation allows validation to be set up.

ActiveSheet.CircleInvalid shows the cells in error, if any.

How can I get a count of the number of cells which are
invalid?

[Norman Jones]

Hi Ajay,

Chip Pearson posted a method for determining invalid entries:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&frame=right&th=132e3583fa
e81b33&seekm=e6UyHFnrBHA.2628%40tkmsftngp03#link4

You could readily adapt Chip's method for your purposes.
For example, adding a counter and amending to exclude blanks as errors:

Sub FindInvalidListData()
Dim Rng As Range
Dim V As Validation
Dim SourceList As Range
Dim Res As Variant
Dim errCount As Long
Dim msg As String
Dim msg2 As String

For Each Rng In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
If Rng.value <> "" Then 'To regard blanks as invalid, delete this
if clause
Set V = Rng.Validation
If V.Type = xlValidateList Then
Set SourceList = Range(Mid(V.Formula1, 2))
Res = Application.Match(Rng.value, SourceList, 0)
If IsError(Res) Then
msg = msg & "Range: " & Rng.Address & _
" has invalid data" & vbNewLine
errCount = errCount + 1
End If
End If
End If
Next Rng
If errCount > 0 Then
msg2 = errCount & " DataValidated cells have invalid entries!" _
& vbNewLine
MsgBox msg, vbInformation, "Invalid Cells"
Else: MsgBox "There are no invalid entries", vbInformation, "Invalid
Cells"
End If
End Sub

---
Regards,
Norman.

"Ajay Askoolum" <ajay.a...@claybrook.co.uk> wrote in message
news:010f01c2dbe4$ff218b10$3301...@phx.gbl...

[Norman Jones]

Hi Ajay,

Please
Change: MsgBox msg, vbInformation, "Invalid Cells"
to: MsgBox msg2 & vbNewLine & msg, vbInformation, "Invalid Cells"

---
Regards,
Norman

"Norman Jones" <norman...@btconnect.com> wrote in message
news:OoILNBB...@TK2MSFTNGP11.phx.gbl...

[Dave Peterson]

And if you didn't consider blank cells as invalid:

Option Explicit
Sub testme01()

Dim myCount As Long
Dim myCell As Range
Dim myRange As Range

Set myRange = Nothing
On Error Resume Next
Set myRange = ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0

If myRange Is Nothing Then
MsgBox "no Validation on this sheet"
Exit Sub
End If

'ActiveSheet.CircleInvalid

myCount = 0
For Each myCell In myRange.Cells
If myCell.Validation.Value = False Then
myCount = myCount + 1
End If
Next myCell

MsgBox "Found invalid: " & myCount & vbLf _
& "From: " & myRange.Cells.Count

End Sub

--

Dave Peterson
ec3...@msn.com