Results 1 to 9 of 9

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

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-26-2004
    Location
    Halifax, UK
    MS-Off Ver
    Office 365 v2402
    Posts
    263

    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
    Last edited by TheRobsterUK; 05-29-2014 at 06:36 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 02-18-2013, 10:17 AM
  2. Error checking: make sure that ID values increase by one digit at a time
    By vzc8 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-21-2011, 11:01 AM
  3. Disable Error Checking for only a range
    By jomili in forum Excel General
    Replies: 2
    Last Post: 11-04-2010, 02:03 PM
  4. Error Handling: Can it report line # where error occurred?
    By PingPing in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-20-2010, 03:47 AM
  5. Way to Turn Off Error Checking on a Range?
    By James Cox in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-01-2005, 03:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1