Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of cells with data validation errors
Hello all,
I have been using the code below to count the number of data validation errors on a sheet (datasheet). It has worked sucessfully over a number of years but all of a sudden when it is run the following message is displayed and the file closes: EXCEL.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log is being created. macro: Sub DVerrors() Application.ScreenUpdating = False Sheets("datasheet").Activate Dim rngDV As Range Dim cell As Range Dim countDV As Long Dim ws As Worksheet Set ws = ActiveSheet On Error GoTo errhandler Set rngDV = ws.Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 countDV = 0 For Each cell In rngDV If Not cell.Validation.Value Then countDV = countDV + 1 End If Next If countDV = 0 Then MsgBox "There are no data validation errors on the sheet.", vbInformation, "Invalid data entries" ActiveSheet.ClearCircles ElseIf countDV = 1 Then MsgBox "There is " & countDV & " data validation error on the sheet.", vbInformation, "Invalid data entry" ActiveSheet.CircleInvalid ElseIf countDV 1 Then MsgBox "There are " & countDV & " data validation errors on the sheet.", vbInformation, "Invalid data entries" ActiveSheet.CircleInvalid End If Exit Sub errhandler: MsgBox "There are no data validation errors on the sheet.", vbInformation, "Invalid data entries" ActiveSheet.ClearCircles Application.ScreenUpdating = True End Sub Thanks in advance. Gareth |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of cells with data validation errors
Gareth,
Your code worked for me on xl2002 (with a small number of validated cells). Some comments... SpecialCells has a limit of 8192 areas that it can return. If you have thousands of validated cells on your worksheet you may be over the limit. It is usually best to loop thru each area in the range returned by SpecialCells... For Each rArea in ws.Cells.SpecialCells(xlCellTypeAllValidation).Are as For Each rCell in rArea.Cells If the problem is limited to just one workbook, it could be corrupt. You would need to build a new workbook. xl2007 has its own rule book designed to encourage users to upgrade from older versions. -- Jim Cone Portland, Oregon USA "Gareth" wrote in message Hello all, I have been using the code below to count the number of data validation errors on a sheet (datasheet). It has worked sucessfully over a number of years but all of a sudden when it is run the following message is displayed and the file closes: EXCEL.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log is being created. macro: Sub DVerrors() Application.ScreenUpdating = False Sheets("datasheet").Activate Dim rngDV As Range Dim cell As Range Dim countDV As Long Dim ws As Worksheet Set ws = ActiveSheet On Error GoTo errhandler Set rngDV = ws.Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 countDV = 0 For Each cell In rngDV If Not cell.Validation.Value Then countDV = countDV + 1 End If Next If countDV = 0 Then MsgBox "There are no data validation errors on the sheet.", vbInformation, "Invalid data entries" ActiveSheet.ClearCircles ElseIf countDV = 1 Then MsgBox "There is " & countDV & " data validation error on the sheet.", vbInformation, "Invalid data entry" ActiveSheet.CircleInvalid ElseIf countDV 1 Then MsgBox "There are " & countDV & " data validation errors on the sheet.", vbInformation, "Invalid data entries" ActiveSheet.CircleInvalid End If Exit Sub errhandler: MsgBox "There are no data validation errors on the sheet.", vbInformation, "Invalid data entries" ActiveSheet.ClearCircles Application.ScreenUpdating = True End Sub Thanks in advance. Gareth |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of cells with data validation errors
jim
Thanks for that. My sheet can have up to 20,000 cells with validation. Are you able to amend my macro so that it will look at all dv cells instead of only the first 8,000 or so just in case it is this that is causing the program error? Thanks. Gareth "Jim Cone" wrote: Gareth, Your code worked for me on xl2002 (with a small number of validated cells). Some comments... SpecialCells has a limit of 8192 areas that it can return. If you have thousands of validated cells on your worksheet you may be over the limit. It is usually best to loop thru each area in the range returned by SpecialCells... For Each rArea in ws.Cells.SpecialCells(xlCellTypeAllValidation).Are as For Each rCell in rArea.Cells If the problem is limited to just one workbook, it could be corrupt. You would need to build a new workbook. xl2007 has its own rule book designed to encourage users to upgrade from older versions. -- Jim Cone Portland, Oregon USA "Gareth" wrote in message Hello all, I have been using the code below to count the number of data validation errors on a sheet (datasheet). It has worked sucessfully over a number of years but all of a sudden when it is run the following message is displayed and the file closes: EXCEL.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log is being created. macro: Sub DVerrors() Application.ScreenUpdating = False Sheets("datasheet").Activate Dim rngDV As Range Dim cell As Range Dim countDV As Long Dim ws As Worksheet Set ws = ActiveSheet On Error GoTo errhandler Set rngDV = ws.Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 countDV = 0 For Each cell In rngDV If Not cell.Validation.Value Then countDV = countDV + 1 End If Next If countDV = 0 Then MsgBox "There are no data validation errors on the sheet.", vbInformation, "Invalid data entries" ActiveSheet.ClearCircles ElseIf countDV = 1 Then MsgBox "There is " & countDV & " data validation error on the sheet.", vbInformation, "Invalid data entry" ActiveSheet.CircleInvalid ElseIf countDV 1 Then MsgBox "There are " & countDV & " data validation errors on the sheet.", vbInformation, "Invalid data entries" ActiveSheet.CircleInvalid End If Exit Sub errhandler: MsgBox "There are no data validation errors on the sheet.", vbInformation, "Invalid data entries" ActiveSheet.ClearCircles Application.ScreenUpdating = True End Sub Thanks in advance. Gareth |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of cells with data validation errors
You could split the UsedRange on the worksheet into 3 or 4 parts and use the SpecialCells method on each separate part. That should eliminate the 8192 area limit. To test whether that can/will cure the problem, you can just change one line of your code... Change: Set rngDV = ws.Cells.SpecialCells(xlCellTypeAllValidation) To: Set rngDV = Selection.SpecialCells(xlCellTypeAllValidation) Then select say a 1/4 of the cells and run the code. -- Jim Cone Portland, Oregon USA "Gareth" wrote in message jim Thanks for that. My sheet can have up to 20,000 cells with validation. Are you able to amend my macro so that it will look at all dv cells instead of only the first 8,000 or so just in case it is this that is causing the program error? Thanks. Gareth "Jim Cone" wrote: Gareth, Your code worked for me on xl2002 (with a small number of validated cells). Some comments... SpecialCells has a limit of 8192 areas that it can return. If you have thousands of validated cells on your worksheet you may be over the limit. It is usually best to loop thru each area in the range returned by SpecialCells... For Each rArea in ws.Cells.SpecialCells(xlCellTypeAllValidation).Are as For Each rCell in rArea.Cells If the problem is limited to just one workbook, it could be corrupt. You would need to build a new workbook. xl2007 has its own rule book designed to encourage users to upgrade from older versions. -- Jim Cone Portland, Oregon USA "Gareth" wrote in message Hello all, I have been using the code below to count the number of data validation errors on a sheet (datasheet). It has worked sucessfully over a number of years but all of a sudden when it is run the following message is displayed and the file closes: EXCEL.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log is being created. macro: Sub DVerrors() Application.ScreenUpdating = False Sheets("datasheet").Activate Dim rngDV As Range Dim cell As Range Dim countDV As Long Dim ws As Worksheet Set ws = ActiveSheet On Error GoTo errhandler Set rngDV = ws.Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 countDV = 0 For Each cell In rngDV If Not cell.Validation.Value Then countDV = countDV + 1 End If Next If countDV = 0 Then MsgBox "There are no data validation errors on the sheet.", vbInformation, "Invalid data entries" ActiveSheet.ClearCircles ElseIf countDV = 1 Then MsgBox "There is " & countDV & " data validation error on the sheet.", vbInformation, "Invalid data entry" ActiveSheet.CircleInvalid ElseIf countDV 1 Then MsgBox "There are " & countDV & " data validation errors on the sheet.", vbInformation, "Invalid data entries" ActiveSheet.CircleInvalid End If Exit Sub errhandler: MsgBox "There are no data validation errors on the sheet.", vbInformation, "Invalid data entries" ActiveSheet.ClearCircles Application.ScreenUpdating = True End Sub Thanks in advance. Gareth |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom data validation on cells with data validation values | Excel Worksheet Functions | |||
Maximum number of data validation cells? | Excel Worksheet Functions | |||
Count cells with numbers and ignore cells with errors | Excel Discussion (Misc queries) | |||
Errors in COUNT, COUNTA, COUNTIF when counting merged cells | Excel Worksheet Functions | |||
Count number of times a specific number is displayed in cells | Excel Programming |