+ Reply to Thread
Results 1 to 29 of 29

.Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.Clear

  1. #1
    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

    .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.Clear

    Application.WorksheetFunction.Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.Clear has no effect.

    Hi .

    . Similar questions to this have been asked before, at least the parts about on error only working once and err.clear being ineffective, to reference just a few
    http://www.excelforum.com/excel-char...king-once.html
    http://www.mrexcel.com/forum/excel-q...orks-once.html
    http://www.mrexcel.com/forum/excel-q...runs-once.html
    .. alternative codes were sometimes given, but the fundamental problem did not seem to be solved

    . ***In short I have many codes which use the described method below (Match / On error combination) and they appeared to work. A current code did not. So I am keen to understand exactly what is going on in case the others working was luck!!!!!

    …. I try to produce a simplified example to help clearly show the issue.:

    . In a sheet Main I list new Food products by their Nutrient values. For new Foods to be added I check for a Heading match, allowing for different spellings etc. of the heading by new Food products.

    . So my Main sheet before running the macro uses the first 10 rows as possible Heading Spellings etc., and looks like this

    Excel 2007
    -
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    name Kcal Fett Eiweiß Koh
    2
    3
    Name
    4
    Description Energie: fat Protein Kohlenhydrate
    5
    Beschreibung kcal Fat eiweiss Kohlenhydrat
    6
    Kilocalorien fette Eiweiß: kohlenhydrate
    7
    Product Kilocalories Fette Carbohydrates
    8
    Name Energy F Proteins Carbohydrates
    9
    No. E
    10
    K
    11
    12
    Select here!
    MainSheet

    . An example of a sheet with a new product to be included in the main sheet would be

    Excel 2007
    -
    A
    B
    1
    name Choc Bar
    2
    3
    Energy
    373
    4
    Proteins
    16
    5
    Carbohydrates
    54
    6
    Sugers
    31
    7
    Fat
    8.8
    8
    Fibre
    5.9
    9
    Sodium
    0.35
    NewFood


    . After running the macro I want this

    Excel 2007
    -
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Kcal Fett Eiweiß Koh
    2
    name
    3
    Name
    4
    Description Energie: fat Protein Kohlenhydrate
    5
    Beschreibung kcal Fat eiweiss Kohlenhydrat
    6
    Kilocalorien fette Eiweiß: kohlenhydrate
    7
    Product Kilocalories Fette Carbohydrates
    8
    Energy F Proteins Carbohydrates
    9
    No. E
    10
    K
    11
    12
    Choc Bar
    373
    8.8
    16
    54
    MainSheet

    . In the following code I attempt to control the error with the On Error GoTo. The code appears only to work for the first error occurrence. Trying Err.Clear in the appropriate place was also ineffective.

    Please Login or Register  to view this content.



    . Can anyone help enlighten me on the fundamental problem here? Why is the above code only catching the error the first time around?? - Mainly for reasons mentioned above*** I wish to sort our this Match / On error GoTo combination problem, but code alternatives would also be welcome….A couple of alternatives I have already and include details at the end.

    Thanks
    Alan
    ...........................

    2 Codes I do have working:
    . 1 ) A code including An obvious extra looping for the header Values works fine ( A Similar Array alternative I have also ):

    . 2) A Code Using On Error Resume Next also works fine :

    Here just code . 2) for comparison ( because of Forum Thread size limitations!! )

    Please Login or Register  to view this content.
    . I enclose the example File I prepared, which includes all three codes in macro module “InputToMainFileSimpleSort”
    ( XL 2007 ) “ InputToMainFileSimpleSort.xlsm”
    https://app.box.com/s/b58naouct28honcsxppjeareq5etfxgp
    (Note for both the working codes to work the first cell in the row required for the New Food input must be selected )

    ………………………………………
    Quote Originally Posted by protonLeah
    that light green font in the code box is very hard to read.
    Hi,
    . Sorry about that.
    . - As most people prefer not to see them it was a compromise for me to attempt to blend them out a bit. (I find I need them if I return to the thread some time later. – (It is sometimes a bit more practical for me to access my codes through the Forums later than getting at my own Back up code Files) )

    . If it helps I have just edited the thread and changed the color to dark green
    . Apologies again for any eye strain
    Alan.
    Attached Files Attached Files
    Last edited by Doc.AElstein; 03-15-2015 at 06:23 AM. Reason: Code Shade darkenned for Proton Leah

  2. #2
    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

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Duplicate Post !? Came up when I tried to edit original ?
    ... can A Moderator delete it please ?
    Last edited by Doc.AElstein; 03-16-2015 at 07:54 AM.
    '_- 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 )

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,811

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Similar problem.

    http://www.excelforum.com/excel-new-...lt-is-n-a.html


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  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

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Quote Originally Posted by TMS View Post

    Hi TMS,
    . Thanks for the reply and the Link.
    . I have reviewed it and shall continue to do so.
    . I apologies but as a Beginner at first glance I do not quite see the significance to my particular question.

    To Summarize:
    . My second Code using On Error Resume Next appears to work and I think I understand it
    . My question was that I could not see why my first code using On Error GoTo only worked once,

    . If you have time could you elaborate on the relevance info from that link?

    Thanks
    Alan Elston
    Last edited by Doc.AElstein; 03-15-2015 at 01:24 PM. Reason: Typo

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,811

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Short answer: I don't know. The point I was making in the linked thread is there is a difference between what happens if you use Application.WorksheetFunction.Match as opposed to Application.Match, although the example is using VLookup, not Match.

    Problem is, I don't understand your data, or logic, or what you would expect to get out. I can make the code run using "On Error Resume Next", but I don't know if the data returned is what you would expect.

    Personally, if you just need to check if something is present in a row/column/range, I'd use a simple CountIf and test for a zero or positive outcome. Then you don't need to worry about the error condition and you can safely use Match or Vlookup, or whatever, to get the data (index) that you want.

    However, I just wouldn't use "If Error Goto Label"

    Please Login or Register  to view this content.

    Regards, TMS

  6. #6
    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

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Hi TMS,
    . I had tried originally to make a simple File to upload to demonstrate a particular question I had rather than the actual code where I had been having problems which were much to large or complicated to upload or show the codes. Sorry if I confused the issue by doing that.

    . Basically if on the file I Uploaded you run this code (that using On Error Resume Next )

    Please Login or Register  to view this content.
    … it does exactly what I want. And I understand why. I tried to explain how this seems to work in the green comments. I am not really handling an error that I do not want. I am expecting the error in the case of no Match found by the Application.Match. I am then using this through the way the error is “Handled” to take the action I require in the case of no Match. There are simply cases in my more complicated codes where this is a “neat way” to do something if, and only if a Match is achieved. So no problem with that code.


    . The problem comes with this code (that using On Error GoTo )

    Please Login or Register  to view this content.
    . If you clear the entered values from the first code on the sheet and reselect cell A12, then step through this code with F8 you will see that it does again exactly what I want once. It puts the a value “Choc Bar” in and then goes on once to “handle” the error as I wish it to. However on the second time around it does not handle the error. It seems to ignore the error handling. This suggests On Error GoTo only works once as opposed to On Error Resume Next which seems to have no limit to how many time it worked. No amount of Googling has yet explained clearly that On Error GoTo only works once.
    . There are simply occasions when I found it convenient to use On Error GoTo . By luck in those codes it was only used once.
    . So the point of my Thread is to get some clarity on On Error GoTo . Often in such a case, the suggestion is given off using Err.Clear to overcome this only working once problem. As with most people I find it ineffective. .. I Put that command (commented out with a in the appropriate point in this last code. If you take out the and step again through the code with F8 you will see that it has no effect

    ……………………..
    .
    . Many thanks for the suggestion of an alternative solution. I am learning so very keen to try out and understand different methods for a solution the variations of which I am finding is endless with VBA. I I have never used CountIf but it sounds like it could be another possibility. I think the “Trick with the “On Error Resume Next / Match Pair” is that it does away with the check for sht2.Cells(NwRw, 1).Value <> "" as the Match also “errors” with that and so I “kill two birds with one stone” with it.

    . Many thanks for taking the effort to write that code. Much appreciated..I have stepped through it many times now with F8. I am not quite sure what you are trying to say with it. I guess I just do not quite understand enough about things like the 0 in On Error GoTo 0
    . Your code basically seems to work the same as that of mine with On Error Resume Next ( Sub SimpleSpreadsheetSort_2_WithMatchErrResumeNextExcelForum() ) and give the correct results as indicated in the Range Screenshots I gave in Post # 1.

    . Many thanks again for your reply. Sorry If I am bad at explaining things! My question still remains as to why my code using On Error GoTo only seems to “catch an error” the first time around.

    Alan

  7. #7
    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

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Quote Originally Posted by TMS View Post
    ……

    Personally, if you just need to check if something is present in a row/column/range, I'd use a simple CountIf and test for a zero or positive outcome. …….

    … Like this: .... ?

    Please Login or Register  to view this content.

    ( AND : It appears to not count (and not error) for empty cells in first argument and / or empty values in second argument ( LookUpValue ) . Great.. Another alternative Thanks for the Tip TMS ..

    Alan

    (P.s. 1 – possibly I missed it as I believe CountIf was not available before XL 2007?, and I may have been reviewing some old articles…?)

    P.s. 2 For anyone following in the future I have updated the Uploaded file with new codes in
    https://app.box.com/s/b58naouct28honcsxppjeareq5etfxgp

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,811

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    ... possibly I missed it as I believe CountIf was not available before XL 2007?
    Yep, I guess you missed it. COUNTIF has always been there, at least as far back as I can remember. COUNTIFS was introduced in Excel 2007, along with SUMIFS and various other functions.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,811

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    My question still remains as to why my code using On Error GoTo only seems to “catch an error” the first time around.
    And my answer remains the same: I don't really know what's going on. If you use Application.WorksheetFunction.Match and the search value is not found, Excel will fail/crash. This can be trapped by using On Error. I would use On Error Resume Next and test the error number in the line following the line of code that may cause the error condition. In theory, you could/can use On error Goto Label and test the error number when you get there. That is then often followed by Resume Next. Either way, you would typically clear the error, either by letting err.number = 0 or, more commonly (I guess), using On Error Goto 0.

    And that is what you did in your working code.

    Conversely, you could just use Application.Match. That will not cause Excel to fail/crash if the search value is not found. It will return an error, "Error 2042", which you can test if you convert the error to a string using CStr. You do not, therefore, need to use error trapping (On Error ...) to trap the error as it just doesn't happen. If you don't change the value returned, and just output it to the worksheet, you'll get #N/A in the cell.

    To test your code, I put Debug.Print after every line of code listing the counters and error value. With the original code, you can see that you get an error (1004) which seems to clear on the next On Error GoTo Label. But then, as you have observed, it doesn't seem to trap the next Match error.

    Please Login or Register  to view this content.
    This is the output. You can see that error 1004 is generated and it is still present starting back through the loop ... but it is cleared between debug 3 and debug 4 ... the "On Error GoTo Here" line.

    Please Login or Register  to view this content.

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,811

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Seeking input from other forum experts ... http://www.excelforum.com/the-water-...ml#post4017217

  11. #11
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Once an error has been encountered, an exception is raised and any applicable error handler becomes active. Until the exception is reset, any further errors are untangled and there are three ways to reset the exception :
    A Resume statement
    Your code ending
    On Error GoTo - 1

    Using Err.Clear only clears the properties of the error, it doesn't reset the original exception. Nor does using on error goto 0.
    Remember what the dormouse said
    Feed your head

  12. #12
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    To illustrate your question you could have made a much less complicated example.

    Your example clearly illustrates that 'structuring precedes coding': it's no use to create complicated code to overcome the deficiencies in the data structure.

    In the presented structure (after removing the empty rows 2)
    Please Login or Register  to view this content.



  13. #13
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    That doesn't answer the question though, it just avoids it.

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,811

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Thanks Rory

    errors are untangled


    That explains a lot, thank you. "On Error GoTo -1" doesn't seem to be widely documented ... at least, I've never com across it. I often use "On Error Resume Next" to trap the (potential) error, test the error number after the line where the error could be generated, and then use "On Error GoTo 0" to clear error handling. I try to limit the error trapping as far as possible and cater for specific errors rather than have a catch-all error handler. Recent reading suggests I should perhaps do both.

    Would you recommend using "On Error GoTo -1" in preference to "On Error GoTo 0"?

    Thanks again.

    Regards, TMS

  15. #15
    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

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    . Thanks Rompy
    .

    Quote Originally Posted by romperstomper View Post
    Once an error has been encountered, an exception is raised and any applicable error handler becomes active. Until the exception is reset, any further errors are untangled and there are three ways to ………..
    Using Err.Clear only clears the properties of the error, it doesn't reset the original exception. Nor does using on error goto 0.
    Thanks. This is the sort of answer that seemed to be lacking in all my Goggling. I am not quite sure what you mean by “clears the properties of the error”, but that Err.Clear is not doing what my Resume Statement does because “It doesn’t Reset as for example a resume statement does” is a good answer!!

    ……………..

    Quote Originally Posted by romperstomper View Post
    …………… there are three ways to reset the exception :
    A Resume statement
    …….
    ….
    ……...
    . Got that. Thanks ! And so now I know that Err.Clear does not “reset the exception”, I have a good code version which “works” and by F8 stepping through demonstrates this….

    Please Login or Register  to view this content.


    Quote Originally Posted by romperstomper View Post
    …………… there are three ways to reset the exception :

    …..
    On Error GoTo - 1

    ……...
    .
    . I am not 100% clear here: …

    .. stepping through with F8 with my On Error GoTo code shows that I catch the error once only, and your explanation has clarified why. Thanks.

    The code again, explained now better in new ‘Comments… thanks to your reply:, thanks!:

    Please Login or Register  to view this content.
    ..But if I change GoTo Here with Goto -1 , then nothing appears to happen, that is to say it errors the first time that the .Match errors (I realist the code here would not do what I want, but I am initially just trying to see here what GoTo – 1 does. It appears to do nothing??)
    . So the question : what exactly does On error GoTo -1 do. Does it reset the error but go back to just before the line where the error occurred? This would then allow one to drag the Yellow cursor in the Left Margin down and further Run the code in F5 or F8 , with the “peace of mind “ that the “exception is reset, any further errors are untangled „


    Thanks, it is all getting clearer now
    Alan

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,811

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    There seem to be two points here. "Why does 'On Error GoTo Here" error trapping not work and producing code that works. Rory has offered an explanation for the first point, and the code below works with your data without generating errors and, thus, not requiring Error Trapping ... in of itself ... not to say you shouldn't still have code to trap (other) errors.

    Please Login or Register  to view this content.

    Regards, TMS

  17. #17
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Untangled was my phone autocorrect version of unhandled!

    I'm too lazy to type code on a phone, which is the only access I have here (still) but I would ignore the On Error GoTo - 1 option. It's not good practice, I've never seen it used but included it for completeness. It is not an error handler of any sort it merely resets a previously handled exception,allowing you to set another error handler if required.

    My error handling preference is to handle as in-line as possible and rarely use generic error handling unless I'm feeling lazy and doing something like turning events off which must get reset.

  18. #18
    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

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Quote Originally Posted by romperstomper View Post
    Untangled was my phone autocorrect version of unhandled!

    I'm too lazy to type code on a phone, which is the only access I have here (still) .......
    I remain amazed that you are still "shut out" And are amazingly grateful that you make such an effort still to reply.



    Quote Originally Posted by romperstomper View Post
    ........I would ignore the On Error GoTo - 1 option. It's not good practice, I've never seen it used but included it for completeness. It is not an error handler of any sort it merely resets a previously handled exception,allowing you to set another error handler if required.......
    Thanks again , I think I follow all that.
    … so is my explanation about right
    Quote Originally Posted by Doc.AElstein View Post
    .......what exactly does On error GoTo -1 do. Does it reset the error but go back to just before the line where the error occurred? This would then allow one to drag the Yellow cursor in the Left Margin down and further Run the code in F5 or F8 , with the “peace of mind “ that the “exception is reset, any further errors are untangled „

    ....................

  19. #19
    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

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Quote Originally Posted by TMS View Post
    .........and the code below works with your data without generating errors and, thus, not requiring Error Trapping ... in of itself ... not to say you shouldn't still have code to trap (other) errors.

    Please Login or Register  to view this content.
    ......
    Hi TMS.
    . Thank you very much for that Code. I had followed your arguments about that ,, Post #5:

    Quote Originally Posted by TMS View Post
    ……. The point I was making in the linked thread is there is a difference between what happens if you use Application.WorksheetFunction.Match as opposed to Application.Match, although ……..
    . As I am still learning and a bit slow it is extremely helpful that you were able to Include that code for me.

    (Here, for fun the “German” version!!!! (Different Error! )

    Please Login or Register  to view this content.
    . By stepping through with F8 I can follow extremely well now your arguments, and I have another code option to consider in the Future! Brilliant, Thanks!

    Alan


    P.s. It is a very minor point but just to explain why I use ActiveCell. I have produced very simple demonstration File here. But based on help from people like you, Rory and LeathRoss to name a few, I have been able to develop my “Real Life “ Project, for example here:
    http://www.mrexcel.com/forum/excel-q...erge-code.html
    ( Sorry I may have PM ed you wrong link there)
    In the last Post from this morning #6 I have referenced 2 large Files.
    . Here I am inserting much data from an internet site into a very large File. I select each time the appropriate line for inserting the new data, using larger more complicated codes based on the basic ideas here.

  20. #20
    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

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Quote Originally Posted by snb View Post
    .......

    In the presented structure (after removing the empty rows 2)
    Please Login or Register  to view this content.
    Hi snb
    . Thanks for the input. I am interested in VBA Array alternatives,. Indeed in my “RealLife” Files I am finding great speed advantages from doing everything “Away from the Sheet” as it were in “VBA Array Routines”.
    . Just some feedback as you took the trouble to post the code. After removing the empty rows 2 the code of yours does not appear to work. Possibly I have missed something. Unfortunately I lack the experience with such a code to Debug it.
    . If you had it working, could you possibly upload a working File so that I can see where I may be going wrong. ( Any explaining ‘Comments… would also be very welcome )
    Thanks
    Alan

  21. #21
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Like any On Error statement, On Error GoTo - 1 doesn't affect which line is processed next, it only clears the current exception.

  22. #22
    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

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Hi Rory,

    Quote Originally Posted by romperstomper View Post
    Like any On Error statement, On Error GoTo - 1 doesn't affect which line is processed next, it only clears the current exception.
    Ok. I think I have it..

    ... so my suggested answer was almost correct?
    Quote Originally Posted by Doc.AElstein View Post
    .......what exactly does On error GoTo -1 do. Does it reset the error but go back to just before the line where the error occurred? This would then allow one to drag the Yellow cursor in the Left Margin down and further Run the code in F5 or F8 , with the “peace of mind “ that the “exception is reset, any further errors are untangled „

    ....................
    .. It does not "go back" to where the error occured as I said, ... it is "stuck there" as it were. So As I mentioned I could drag it past the error and carry on as if nothing had happened as On Error GoTo - 1 "clears the current exception." - exactly what "clears the current exception." is a bit advanced for me, but I guess "carry on as if nothing had happened " is a resonable explanation?

    Thanks again
    Alan

  23. #23
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Kind of. I will try and post a better explanation later but in short GoTo - 1 clears the error but leaves the same error handler enabled (but no longer active); GoTo 0 disables any enabled error handler (active or not) but does not clear any active error. Err.Clear doesn't alter either the error handler or the error status but merely clears the last error information from the Err object. I really ought to blog this properly!

  24. #24
    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

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Hi TMS,
    . I have been learning lots from this Thread and I am sure I and others will continue to do so.
    …just a very minor Pont. I have now tested and used extensively your code from Post #9. (Thanks very much for writing such a detailed “Error number analysis” code. ).

    Quote Originally Posted by TMS View Post
    ……….
    ……..

    This is the output. You can see that error 1004 is generated and it is still present starting back through the loop ... but it is cleared between debug 3 and debug 4 ... the "On Error GoTo Here" line……

    …just to get it clear in my head.


    . a)…… going Through as it were On Error GoTo somehow “clears the error” – your last point. In the above. I note also that by running that code again (Application.WorksheetFunction.Match version ) with the Err.Clear back in also “Clears the error”after it occurred and indeed running that code gives no error in the Debug Direct Window
    . But we understand now that “clearing “ is not what is needed. We “cleared” for example the err.Number property, hence it goes away from the Debug Direct Window, ( Or rasther Err.Number returns 0 which I guess is the default for no error ). Hence one can use this property again, although difficult as you cannt error again until the error is "reset" which clerars the property proberly anyway!!?!)

    . b) What we need to as Rory said “untangle and reset the exception“.

    ……i have it almost, I think!!!
    ………………………..

  25. #25
    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

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Quote Originally Posted by romperstomper View Post
    Kind of. I will try and post a better explanation later ……….. I really ought to blog this properly!
    . Thanks Again.
    . Please do “Blog at your convenience”

  26. #26
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,811

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    We seem to be going around in circles now. I have explained that my preferred approach is local/limited use of "On Error Resume Next" with "On Error GoTo 0". My understanding is that "On Error GoTo 0" both clears the current error status AND de-activates the current error handler.

    It is apparent that the code you are investigating clears the error properties (number, description, etc), but that it does not reset the outstanding error. Although it has been an interesting discussion, it does not seem to be moving us forward, and I would suggest it is time to draw a line.


    What we need to as Rory said “untangle and reset the exception“.
    I think that Rory was trying to say "unhandled" but his phone wouldn't let him.


    As I have said before, you have been provided with a variety of ways of avoiding, or overcoming, the error(s) raised by the MATCH function failing. I think it is time to accept that some methods work, and others don't, to choose one of the methods that does work, and put the others behind you.


    Regards, TMS

  27. #27
    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

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Quote Originally Posted by TMS View Post
    .......Although it has been an interesting discussion, it does not seem to be moving us forward, and I would suggest it is time to draw a line........

    As I have said before, you have been provided with a variety of ways of avoiding, or overcoming, the error(s) raised by the MATCH function failing. I think it is time to accept that some methods work, and others don't, to choose one of the methods that does work, and put the others behind you.

    Regards, TMS
    . OK. Thanks again for all your help!
    .
    . ..Blogging - off for a bit then here.

    … just a quick last tidy up… The “Array” solution popped up as an alternative (nothing to do with the error stuff..)…
    .. I still could not get that snb code to work ( probably down to me??) . But anyways 2 Final codes working on the example File (File I have updated appropriately) . The simplest possible “Spreadsheet” type code I wanted to post at the start but the Thread size limitation snookered me. And straight after it the equivalent Array version. Not as clever as snb’s. But I can understand it and get it to work!!

    ..

    Please Login or Register  to view this content.
    …………………………………………………………………………………….

    File again
    https://app.box.com/s/b58naouct28honcsxppjeareq5etfxgp
    Last edited by Doc.AElstein; 03-16-2015 at 11:25 AM. Reason: Forgot File

  28. #28
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    My understanding is that "On Error GoTo 0" both clears the current error status AND de-activates the current error handler.
    It does the latter but not the former, though you are unlikely to ever notice.

  29. #29
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,811

    Re: .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.C

    Thanks Rory. I obviously never have noticed! The approach has always done what I expected ... perhaps my expectations were slightly over ambitious.

    I have to be honest and say that I have got into the habit of clearing a variable before trying to set it so that I can test the null/zero/empty condition with confidence.

    Thanks again.

+ 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. [SOLVED] VBA help needed to capture Error and Remove Pivot if error exist else resume
    By Tescatlipoca in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-18-2014, 05:46 PM
  2. VBA error question (Expected expression) Application.Worksheetfunction
    By Gijsbenjezelf in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-04-2014, 12:40 PM
  3. Application.Worksheetfunction.match ERROR
    By Guido Meng in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-28-2013, 01:27 PM
  4. Error pops up on line On Error Resume Next
    By Erusso in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-20-2012, 04:57 PM
  5. [SOLVED] worksheetfunction countifs, counting down and across, application defined error
    By Lifeseeker in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-16-2012, 11:58 AM

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