+ Reply to Thread
Results 1 to 9 of 9

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

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

    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:

    Please Login or Register  to view this content.
    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.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    This needs to go within the worksheet loop.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    05-26-2004
    Location
    Halifax, UK
    MS-Off Ver
    Office 2016
    Posts
    260

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

    Hi Norie,

    Okay I tried that but it still reports every worksheet as failing the test (range E3:E33 not containing a 1 or 0).

    Cheers

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Where exactly did you put that code?

    Did you definitely include the worksheet reference, ws?

  5. #5
    Forum Contributor
    Join Date
    05-26-2004
    Location
    Halifax, UK
    MS-Off Ver
    Office 2016
    Posts
    260

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

    Code as follows:

    Please Login or Register  to view this content.
    Not the right place?

  6. #6
    Forum Contributor
    Join Date
    05-26-2004
    Location
    Halifax, UK
    MS-Off Ver
    Office 2016
    Posts
    260

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

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

    Please Login or Register  to view this content.
    I changed it to look for cells that equal zero

    Please Login or Register  to view this content.
    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)
    Last edited by TheRobsterUK; 05-29-2014 at 06:21 AM.

  7. #7
    Forum Contributor
    Join Date
    05-26-2004
    Location
    Halifax, UK
    MS-Off Ver
    Office 2016
    Posts
    260

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

    Okay I think I see the mistake in my logic...use of the Or statement. Of course each value will NOT EQUAL 0 and 1 at the same time.

    Need a way of saying if any of the values do not equal a 1 or 0 then make note of it...just need to assess it differently...

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

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

    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.
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    05-26-2004
    Location
    Halifax, UK
    MS-Off Ver
    Office 2016
    Posts
    260

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

    Works a treat now.

    Mistake was so obvious too....must have been having a blonde moment.

    Cheers
    Rob

+ Reply to Thread

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