Error checking routine - check range values on various worksheets and report if error

Error Check Marco.xlsm

Please see attached example. I am trying to write an error detection routine that iterates through worksheets that have numeric values for names (ignore text names or alphanumeric). Macro checks range on each numeric worksheet E3:E33 and is supposed to report back on the SummarySheet if any value other than 1 or 0 is found in range E3:E33 on any numeric-name worksheet. Code as follows:

Sub error_checks()
'routine for checking for a range of possible user-derived errors

'dim vars
Dim ws As Worksheet
Dim cell As Range
Dim include_range As Range
Dim include_range_error_flag As Integer      'reports if the values in Include? column are not a 1 or a 0

'set var values
include_range_error_flag = 0

'clear previous error reports
Range("L28:N2000").ClearContents

'use Set command to set the range of include_range (is the same on every numeric-name sheet, E3:E33)
Set include_range = Range("E3:E33")

'Check that the 'Include?' column only contains 1's or 0's
For Each ws In Worksheets
 If ws.Name Like String(Len(ws.Name), "#") Then     'check if worksheet name is numeric, only want to perform certain error checks on numeric tabs
  For Each cell In include_range
   If cell.Value <> 0 Or cell.Value <> 1 Then include_range_error_flag = 1  'wrong value in Include range so make error flag = 1
  Next cell  'loop through each cell in include_range
 End If
  
 If include_range_error_flag = 1 Then   'if error flag = 1 then record worksheet name and reason for error
  Sheets("SummarySheet").Range("L60000").End(xlUp).Offset(1, 0) = ws.Name
  Sheets("SummarySheet").Range("N60000").End(xlUp).Offset(1, 0) = "Include column not 1 or 0"
 End If
 
 include_range_error_flag = 0   'reset error flag ready for next worksheet

Next    'assess next worksheet

End Sub

Problem is that it just reports EVERY worksheet as having an error when clearly most don't (none do I think in the attached example).

Try changing some of ranges E3:E33 to values other than 1 or 0, it still reports all sheets. Can anyone shed any light on why the macro does not evaluate the range E3:E33 properly and just reports every worksheet as having an error?

Thanks
Rob

This needs to go within the worksheet loop.

 
Set include_range = ws.Range("E3:E33")

Where exactly did you put that code?

Did you definitely include the worksheet reference, ws?

I think it might have something to do with this line:

If cell.Value <> 0 Or cell.Value <> 1 Then include_range_error_flag = 1  'wrong value in Include range so make error flag = 1

I changed it to look for cells that equal zero

If cell.Value = 0 Then include_range_error_flag = 1  'wrong value in Include range so make error flag = 1

This seems to work, i.e. if I change some of the ranges E3:E33 to zeros it reports those worksheet names, as it should. But the original line doesn't seem to work (look for values that are not a 1 OR 0)

The If statement is wrong, it should be And not Or.

Also, you should exit the inner loop as soon as an error is flagged.

Option Explicit

Sub error_checks()
'routine for checking for a range of possible user-derived errors

'dim vars
Dim ws As Worksheet
Dim cell As Range
Dim include_range As Range
Dim include_range_error_flag As Integer      'reports if the values in Include? column are not a 1 or a 0. Boolean?

    'clear previous error reports
    Worksheets("SummarySheet").Range("L28:N2000").ClearContents

    'Check that the 'Include?' column only contains 1's or 0's
    For Each ws In Worksheets
    'use Set command to set the range of include_range (is the same on every DCM data sheet, E3:E33)

        Set include_range = ws.Range("E3:E33")
        If ws.Name Like String(Len(ws.Name), "#") Then     'check if worksheet name is numeric, only want to perform certain error checks on numeric tabs
            For Each cell In include_range
                If cell.Value <> 0 And cell.Value <> 1 Then
                    include_range_error_flag = 1
                    Exit For
                End If 'wrong value in Include range so make error flag = 1
            Next cell  'loop through each cell in include_range
        End If

        If include_range_error_flag = 1 Then   'if error flag = 1 then record worksheet name and reason for error
            Sheets("SummarySheet").Range("L65000").End(xlUp).Offset(1, 0) = ws.Name
            Sheets("SummarySheet").Range("N65000").End(xlUp).Offset(1, 0) = "Include column not 1 or 0"
        End If

        include_range_error_flag = 0   'reset error flag ready for next worksheet

    Next    'assess next worsheet

End Sub