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

………………………………………

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.

Duplicate Post !? Came up when I tried to edit original ?
... can A Moderator delete it please ?

Similar problem.

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


Regards, TMS

Originally Posted by TMS

Similar problem.

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


Regards, TMS

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

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

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

Originally Posted by TMS

……

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

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

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.

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

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.

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.

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

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

. Thanks Rompy
.

Originally Posted by romperstomper

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!!

……………..

Originally Posted by romperstomper

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

Originally Posted by romperstomper

…………… 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

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

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.

Originally Posted by romperstomper

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.

Originally Posted by romperstomper

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

Originally Posted by Doc.AElstein

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

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

Originally Posted by TMS

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

Originally Posted by TMS

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

Originally Posted by snb

.......

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

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

Hi Rory,

Originally Posted by romperstomper

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?

Originally Posted by Doc.AElstein

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

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!

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

Originally Posted by TMS

……….
……..

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!!!
………………………..

Originally Posted by romperstomper

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”

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

Originally Posted by TMS

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

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.

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.