+ Reply to Thread
Results 1 to 4 of 4

OnError works once, then doesn't

  1. #1
    Registered User
    Join Date
    01-16-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    14

    OnError works once, then doesn't

    Thanks beforehand.

    Rather then find a workaround I'd really like to learn why this isn't working.

    Simple macro to loop through all worksheets. Those worksheets who's title doesn't start with two digits, skip over.

    Those that do, work down the worksheet, if C column entry = "H", hide row.

    Please Login or Register  to view this content.
    First pass, works fine. Second pass - Run-time error '13': Type mismatch. No duh.

    (I is public variable.)

    Frustrated, thanks for the relief.

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

    Re: OnError works once, then doesn't

    You would need to add On Error Goto 0 to reset the error handler.

    Mind you, couldn't you use IsNumeric?

    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    01-16-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: OnError works once, then doesn't

    Reset the error handler. All my research did not illuminate this point...

    Your code is cleaner. Thanks.

    BTW, had to remove the Not

    Thanks again.

  4. #4
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    OnError GoTo __ works once, then doesn't? - Need to clear the errected exception.. ;)


    Quote Originally Posted by Adison View Post
    ...Rather than find a workaround I'd really like to learn why this isn't working......
    _...Reset the error handler? All my research did not illuminate this point...
    It is just a peculiar thing in VBA:
    When an error occurs, an “exceptional state of being is raised”.
    Amongst other things VBA is “thinking” that somehow an error is being dealt with,
    either
    _ Via the default one
    or, as in your case,
    _ Via the one you defined.

    Because of this “thinking” , VBA ignores all other Error handling statements.
    That includes for example, ignoring any On Error GoTo 0. The error handling statement On Error GoTo 0 would normally disable the current error handler, in this case your defined error handler. But that will not work once an error has occurred , for the reasons I explained.

    The error handler statement you need is On Error GoTo -1. In your original code that would go here.:
    Please Login or Register  to view this content.
    Further explanations Here:
    http://excelmatters.com/2015/03/17/on-error-wtf/
    https://app.box.com/s/8zkhjcmbxrqnlnexqpktuy41clgqm4zo
    Some codes to work through to demo all about error handling:
    https://www.excelforum.com/developme...ml#post4530379

    _._______________-

    If you read those links, you will see that you could alternatively use your original code with simply replacing your error handler with
    On Error Resume Next

    But, it is better practice, anyway, to use a code that does not require error handling

    Alan
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

+ 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. Need help debugging an Add-In that sometimes works and sometimes doesn't
    By damjanovic in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-10-2014, 05:19 PM
  2. My VBA Macro Sometimes Works and Sometimes Doesn't
    By chsaav in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-22-2013, 09:10 PM
  3. Code that sometimes works and sometimes doesn't
    By kat14 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-06-2013, 05:20 PM
  4. [SOLVED] Sub works sometimes and sometimes doesn't
    By Barry O''Connell in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-18-2006, 10:10 PM
  5. [SOLVED] it works, it doesn work, its works....and so on.
    By Naz in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-30-2005, 09:05 AM

Tags for this Thread

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