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
Bookmarks