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

Sub SimpleSpreadsheetSortWithMatchErrGotoExcelForum()
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("MainSheet"): Set sht2 = ThisWorkbook.Worksheets("NewFood")
Dim MnCm As Long, NwRw As Long, HdNu 'Numbers for Main Sheet Column , New Sheet Row and , Heading Number

    For NwRw = 1 To 20 'Go down at least enough New Product Rows
        For MnCm = 1 To 11 'Go Through at least enough Main Sheet Columns
            
                If sht2.Cells(NwRw, 1).Value <> "" Then  'Check to avoid looking for matching
                    On Error GoTo Here
                    If Application.WorksheetFunction.Match(sht2.Cells(NwRw, 1).Value, sht1.Columns(MnCm), 0) <> 0 Then 'Heading match conditions = 1,  2,, 3... etc.  so:...
                    Let ActiveCell.Offset(0, MnCm - 1).Value = sht2.Cells(NwRw, 2).Value
Here:               'Err.Clear
                    Else 'No Heading Match condition, do Nothing
                    End If
                Else 'No Entry in New Food Sheet
                End If
            
        Next MnCm
    Next NwRw

End Sub 'SimpleSpreadsheetSortWithMatchErrGotoExcelForum

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

Sub SimpleSpreadsheetSort_2_WithMatchErrResumeNextExcelForum()
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("MainSheet"): Set sht2 = ThisWorkbook.Worksheets("NewFood")
Dim MnCm As Long, NwRw As Long, HdNu 'Numbers for Main Sheet Column , New Sheet Row and , Heading Number

    For NwRw = 1 To 20 'Go down at least enough New Product Rows
        For MnCm = 1 To 11 'Go Through at least enough Main Sheet Columns
                    On Error Resume Next 'This works such that if we error at the next line we go directly to the next line under this errored line
                    If Application.WorksheetFunction.Match(sht2.Cells(NwRw, 1).Value, sht1.Columns(MnCm), 0) = -23456 Then  '-23456 is an abritruary number App.Match will error for both empty new Food Row or Match indice number other than 1, 2, 3  .... etc
                    'No Action. We error but resume next, so go on to "next" which is here or rather go to EndIF as nothing here
                    Else ' App.Match "worked" (gave 1, 2, 3  ... etc), so match condition, but not -23456, so:
                    Let ActiveCell.Offset(0, MnCm - 1).Value = sht2.Cells(NwRw, 2).Value 'Insert in appropriote cell the Name or Nutrition value
                    End If
        Next MnCm
    Next NwRw

End Sub 'SimpleSpreadsheetSort_2_WithMatchErrResumeNextExcelForum

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

Sub SimpleSpreadsheetSortWithMatchErrGotoExcelForum()
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("MainSheet"): Set sht2 = ThisWorkbook.Worksheets("NewFood")
Dim MnCm As Long, NwRw As Long, HdNu 'Numbers for Main Sheet Column , New Sheet Row and , Heading Number
Dim vData

For NwRw = 1 To 20 'Go down at least enough New Product Rows
    For MnCm = 1 To 11 'Go Through at least enough Main Sheet Columns
        
        If sht2.Cells(NwRw, 1).Value <> "" Then  'Check to avoid looking for matching
            Debug.Print "NwRw: " & NwRw, "MnCm: " & MnCm, "look for: " & sht2.Cells(NwRw, 1).Value, "search column: " & sht1.Columns(MnCm).Address
            
            On Error Resume Next
            vData = 0: vData = Application.WorksheetFunction.Match(sht2.Cells(NwRw, 1).Value, sht1.Columns(MnCm), 0): Debug.Print "vData: " & vData
            If vData <> 0 Then 'Heading match conditions = 1,  2,, 3... etc.  so:...
            
                ' test the error number and take appropriare action here
                Debug.Print "Err.Number (before reset): " & Err.Number: On Error GoTo 0: Debug.Print "Err.Number (after reset): " & Err.Number
                Let ActiveCell.Offset(0, MnCm - 1).Value = sht2.Cells(NwRw, 2).Value
            
            Else 'No Heading Match condition, do Nothing (redundant code)
                End If
        
        Else 'No Entry in New Food Sheet (redundant code)
        End If
    
    Next MnCm
Next NwRw

End Sub 'SimpleSpreadsheetSortWithMatchErrGotoExcelForum

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 )

Sub SimpleSpreadsheetSort_2_WithMatchErrResumeNextExcelForum()
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("MainSheet"): Set sht2 = ThisWorkbook.Worksheets("NewFood")
Dim MnCm As Long, NwRw As Long, HdNu 'Numbers for Main Sheet Column , New Sheet Row and , Heading Number

    For NwRw = 1 To 20 'Go down at least enough New Product Rows
        For MnCm = 1 To 11 'Go Through at least enough Main Sheet Columns
                    On Error Resume Next 'This works such that if we error at the next line we go directly to the next line under this errored line
                    If Application.WorksheetFunction.Match(sht2.Cells(NwRw, 1).Value, sht1.Columns(MnCm), 0) = -23456 Then  '-23456 is an abritruary number App.Match will error for both empty new Food Row or Match indice number other than 1, 2, 3  .... etc
                    'No Action. We error but resume next, so go on to "next" which is here or rather go to EndIF as nothing here
                    Else ' App.Match "worked" (gave 1, 2, 3  ... etc), so match condition, but not -23456, so:
                    Let ActiveCell.Offset(0, MnCm - 1).Value = sht2.Cells(NwRw, 2).Value 'Insert in appropriote cell the Name or Nutrition value
                    End If
        Next MnCm
    Next NwRw

End Sub 'SimpleSpreadsheetSort_2_WithMatchErrResumeNextExcelForum

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

Sub SimpleSpreadsheetSortWithMatchErrGotoExcelForum()
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("MainSheet"): Set sht2 = ThisWorkbook.Worksheets("NewFood")
Dim MnCm As Long, NwRw As Long, HdNu 'Numbers for Main Sheet Column , New Sheet Row and , Heading Number

    For NwRw = 1 To 20 'Go down at least enough New Product Rows
        For MnCm = 1 To 11 'Go Through at least enough Main Sheet Columns
            
                If sht2.Cells(NwRw, 1).Value <> "" Then  'Check to avoid looking for matching
                    On Error GoTo Here
                    If Application.WorksheetFunction.Match(sht2.Cells(NwRw, 1).Value, sht1.Columns(MnCm), 0) <> 0 Then 'Heading match conditions = 1,  2,, 3... etc.  so:...
                    Let ActiveCell.Offset(0, MnCm - 1).Value = sht2.Cells(NwRw, 2).Value
Here:               'Err.Clear
                    Else 'No Heading Match condition, do Nothing
                    End If
                Else 'No Entry in New Food Sheet
                End If
            
        Next MnCm
    Next NwRw

End Sub 'SimpleSpreadsheetSortWithMatchErrGotoExcelForum

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

Sub SimpleSpreadsheetSortWithCountIfExcelForumTMS()
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("MainSheet"): Set sht2 = ThisWorkbook.Worksheets("NewFood")
Dim MnCm As Long, NwRw As Long, HdNu 'Numbers for Main Sheet Column , New Sheet Row and , Heading Number
Dim CntsIf As Long 'Variable for number of "matches" in headings
    For NwRw = 1 To 20 'Go down at least enough New Product Rows
        For MnCm = 1 To 11 'Go Through at least enough Main Sheet Columns
                'If sht2.Cells(NwRw, 1).Value <> "" Then  'Check to avoid looking for matching
                  Let CntsIf = Application.WorksheetFunction.CountIf(sht1.Columns(MnCm), sht2.Cells(NwRw, 1).Value) 'We "Count If" string in second argument is in the Array or Range of the first argument. And it does not seem to count empty cells
                    If CntsIf <> 0 Then 'If count is 1, 2 , 3 ...etc then we have match or matches..so...
                    Let ActiveCell.Offset(0, MnCm - 1).Value = sht2.Cells(NwRw, 2).Value 'Put Nutrient value in appropriate cell
                    Else 'No Heading Match condition, do Nothing (redundant code)
                    End If
                'Else 'No Entry in New Food Sheet (redundant code)
                'End If
        Next MnCm
    Next NwRw

End Sub 'SimpleSpreadsheetSortWithCountIfExcelForumTMS

( 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

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.

Sub SimpleSpreadsheetSortWithMatchErrGotoExcelForum()
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("MainSheet"): Set sht2 = ThisWorkbook.Worksheets("NewFood")
Dim MnCm As Long, NwRw As Long, HdNu 'Numbers for Main Sheet Column , New Sheet Row and , Heading Number

For NwRw = 1 To 20 'Go down at least enough New Product Rows
    Debug.Print 1, "NwRw: "; NwRw, "MnCm: " & MnCm, "err.number: " & Err.Number
    For MnCm = 1 To 11 'Go Through at least enough Main Sheet Columns
        Debug.Print 2, "NwRw: "; NwRw, "MnCm: " & MnCm, "err.number: " & Err.Number
        
        If sht2.Cells(NwRw, 1).Value <> "" Then  'Check to avoid looking for matching
            Debug.Print 3, "NwRw: "; NwRw, "MnCm: " & MnCm, "err.number: " & Err.Number
            On Error GoTo Here
            Debug.Print 4, "NwRw: "; NwRw, "MnCm: " & MnCm, "err.number: " & Err.Number
            Dim x: x = 0: x = Application.WorksheetFunction.Match(sht2.Cells(NwRw, 1).Value, sht1.Columns(MnCm), 0) ' <<< this fails
            'Dim x: x = 0: x = Application.Match(sht2.Cells(NwRw, 1).Value, sht1.Columns(MnCm), 0)
            Debug.Print 5, "NwRw: "; NwRw, "MnCm: " & MnCm, "err.number: " & Err.Number
            If CStr(x) <> "Error 2042" Then
                If x > 0 Then 'Heading match conditions = 1,  2,, 3... etc.  so:...
                    Debug.Print 6, "NwRw: "; NwRw, "MnCm: " & MnCm, "err.number: " & Err.Number
                    Let sht1.Range("A12").Offset(0, MnCm - 1).Value = sht2.Cells(NwRw, 2).Value
                    Debug.Print 7, "NwRw: "; NwRw, "MnCm: " & MnCm, "err.number: " & Err.Number
Here:               'Err.Clear
                    Debug.Print 8, "NwRw: "; NwRw, "MnCm: " & MnCm, "err.number: " & Err.Number
                End If
            Else 'No Heading Match condition, do Nothing
            End If
        Else 'No Entry in New Food Sheet
        End If
    
    Next MnCm
Next NwRw

End Sub 'SimpleSpreadsheetSortWithMatchErrGotoExcelForum

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.

 1            NwRw:  1      MnCm: 0       err.number: 0 
 2            NwRw:  1      MnCm: 1       err.number: 0 
 3            NwRw:  1      MnCm: 1       err.number: 0 
 4            NwRw:  1      MnCm: 1       err.number: 0 
 5            NwRw:  1      MnCm: 1       err.number: 0 
 6            NwRw:  1      MnCm: 1       err.number: 0 
 7            NwRw:  1      MnCm: 1       err.number: 0 
 8            NwRw:  1      MnCm: 1       err.number: 0 
 2            NwRw:  1      MnCm: 2       err.number: 0 
 3            NwRw:  1      MnCm: 2       err.number: 0 
 4            NwRw:  1      MnCm: 2       err.number: 0 
 8            NwRw:  1      MnCm: 2       err.number: 1004 Unable to get the Match property of the WorksheetFunction class
 2            NwRw:  1      MnCm: 3       err.number: 1004 Unable to get the Match property of the WorksheetFunction class
 3            NwRw:  1      MnCm: 3       err.number: 1004 Unable to get the Match property of the WorksheetFunction class
 4            NwRw:  1      MnCm: 3       err.number: 0

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

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

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.

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

'
Sub SimpleSpreadsheetSorWithMatchErrGotoResumeNextExcelForum()
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("MainSheet"): Set sht2 = ThisWorkbook.Worksheets("NewFood")
Dim MnCm As Long, NwRw As Long, HdNu 'Numbers for Main Sheet Column , New Sheet Row and , Heading Number

    For NwRw = 1 To 20 'Go down at least enough New Product Rows
        For MnCm = 1 To 11 'Go Through at least enough Main Sheet Columns
                    On Error GoTo Anywheres 'Go to "Anywhere" to reset "the exception"
                    If Application.WorksheetFunction.Match(sht2.Cells(NwRw, 1).Value, sht1.Columns(MnCm), 0) = -23456 Then  '-23456 is an abritruary number App.Match will error for both empty new Food Row or Match indice number other than 1, 2, 3  .... etc
                    'No Action. We error but resume next, so go on to "next" which is here or rather go to EndIF as nothing here
                    Else ' App.Match "worked" (gave 1, 2, 3  ... etc), so match condition, but not -23456, so:
                    Let ActiveCell.Offset(0, MnCm - 1).Value = sht2.Cells(NwRw, 2).Value 'Insert in appropriote cell the Name or Nutrition value
                    End If
        Next MnCm
    Next NwRw

Exit Sub
Anywheres: Resume Next 'This works such that if we error we go directly to the next line under that where the error occurred

End Sub 'SimpleSpreadsheetSorWithMatchErrGotoResumeNextExcelForum

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

Sub SimpleSpreadsheetSortWithMatchErrGotoRoaryExcelForum()
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("MainSheet"): Set sht2 = ThisWorkbook.Worksheets("NewFood")
Dim MnCm As Long, NwRw As Long, HdNu 'Numbers for Main Sheet Column , New Sheet Row and , Heading Number

    For NwRw = 1 To 20 'Go down at least enough New Product Rows
        For MnCm = 1 To 11 'Go Through at least enough Main Sheet Columns
            
                If sht2.Cells(NwRw, 1).Value <> "" Then  'Check to avoid looking for matching
                    On Error GoTo Here 'This will work once the first time the error is encountered im .Match as Error go to does not "reset the original exception" ..Post #11 here  http://www.excelforum.com/excel-new-users-basics/1072093-match-with-on-error-on-error-resume-next-works-on-error-goto-only-works-once-err-clear.html
                    If Application.WorksheetFunction.Match(sht2.Cells(NwRw, 1).Value, sht1.Columns(MnCm), 0) <> 0 Then 'Heading match conditions = 1,  2,, 3... etc.  so:...
                    Let ActiveCell.Offset(0, MnCm - 1).Value = sht2.Cells(NwRw, 2).Value
Here:               'Err.Clear 'Using Err.Clear only clears the properties of the error, it doesn't reset the original exception.
                    Else 'No Heading Match condition, do Nothing
                    End If
                Else 'No Entry in New Food Sheet
                End If
            
        Next MnCm
    Next NwRw

End Sub 'SimpleSpreadsheetSortWithMatchErrGotoRoaryExcelForum

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

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)

Sub M_snb()
  sp = Tabelle2.Cells(1).CurrentRegion
  sq = Tabelle1.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(1, 28)
  
  sq(1, 1) = sp(1, 2)
  For j = 2 To UBound(sp)
    sq(1, Tabelle1.UsedRange.Find(sp(j, 1)).Column) = sp(j, 2)
  Next
  
  Tabelle1.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(1, 28) = sq
End Sub

Originally Posted by snb

.......

In the presented structure (after removing the empty rows 2)

Sub M_snb()
  sp = Tabelle2.Cells(1).CurrentRegion
  sq = Tabelle1.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(1, 28)
  
  sq(1, 1) = sp(1, 2)
  For j = 2 To UBound(sp)
    sq(1, Tabelle1.UsedRange.Find(sp(j, 1)).Column) = sp(j, 2)
  Next
  
  Tabelle1.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(1, 28) = sq
End Sub

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

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

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.

Sub SimpleSpreadsheetSortWithMatchErrGotoExcelForum()
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("MainSheet"): Set sht2 = ThisWorkbook.Worksheets("NewFood")
Dim MnCm As Long, NwRw As Long, HdNu 'Numbers for Main Sheet Column , New Sheet Row and , Heading Number

For NwRw = 1 To 20 'Go down at least enough New Product Rows
    For MnCm = 1 To 11 'Go Through at least enough Main Sheet Columns
        If sht2.Cells(NwRw, 1).Value <> "" Then  'Check to avoid looking for matching
            Dim x: x = 0: x = Application.Match(sht2.Cells(NwRw, 1).Value, sht1.Columns(MnCm), 0)
            If CStr(x) <> "Error 2042" Then
                If x > 0 Then 'Heading match conditions = 1,  2,, 3... etc.  so:...
                    Let sht1.Range("A12").Offset(0, MnCm - 1).Value = sht2.Cells(NwRw, 2).Value
                End If
            End If
        End If
    Next MnCm
Next NwRw

End Sub 'SimpleSpreadsheetSortWithMatchErrGotoExcelForum

Regards, TMS

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.

Sub .............
. ...
.   ..............

......

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

'
'
Sub SimpleSpreadsheetSortWithAppMatchTMS()
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("MainSheet"): Set sht2 = ThisWorkbook.Worksheets("NewFood")
Dim MnCm As Long, NwRw As Long, HdNu 'Numbers for Main Sheet Column , New Sheet Row and , Heading Number

For NwRw = 1 To 20 'Go down at least enough New Product Rows
    For MnCm = 1 To 11 'Go Through at least enough Main Sheet Columns
        If sht2.Cells(NwRw, 1).Value <> "" Then  'Check to avoid looking for matching
            Dim x: x = 0: x = Application.Match(sht2.Cells(NwRw, 1).Value, sht1.Columns(MnCm), 0)
            If CStr(x) <> "Fehler 2042" Then
                If x > 0 Then 'Heading match conditions = 1,  2,, 3... etc.  so:...
                    Let ActiveCell.Offset(0, MnCm - 1).Value = sht2.Cells(NwRw, 2).Value
                End If
            End If
        End If
    Next MnCm
Next NwRw

End Sub 'SimpleSpreadsheetSortWithAppMatchTMS

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

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 „

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

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!

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

..

Sub SimpleSpreadsheetSort_1_ExcelForum()
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("MainSheet"): Set sht2 = ThisWorkbook.Worksheets("NewFood")
Dim MnCm As Long, NwRw As Long, HdNu 'Numbers for Main Sheet Column , New Sheet Row and , Heading Number

    For NwRw = 1 To 20 'Go down at least enough New Product Rows
        For MnCm = 1 To 11 'Go Through at least enough Main Sheet Columns
            For HdNu = 1 To 10 'Go through heading Spelling Possibilities
                If sht2.Cells(NwRw, 1).Value <> "" Then  'Check to avoid looking for matching
                    If sht2.Cells(NwRw, 1).Value = sht1.Cells(HdNu, MnCm).Value Then 'Heading match conditions so:...
                    Let ActiveCell.Offset(0, MnCm - 1).Value = sht2.Cells(NwRw, 2).Value '...Write in Appropriate Name or Nutrition Value
                    Else 'No Heading Match condition, so do Nothing
                    End If
                Else 'No Entry in New Food Sheet
                End If
            Next HdNu
        Next MnCm
    Next NwRw

End Sub 'SimpleSpreadsheetSort_1_ExcelForum


Sub SimpleArraySort_1_ExcelForum()
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("MainSheet"): Set sht2 = ThisWorkbook.Worksheets("NewFood")
Dim MnCm As Long, NwRw As Long, HdNu 'Numbers for Main Sheet Column , New Sheet Row and , Heading Number
Dim ArrFd() As Variant, ArrMn() As Variant 'Array required for "Captured" Ranges. Variant to see range Object in ine Liner "Capture", and for differnt indicie types
Let ArrFd() = sht2.Range("A1:B" & sht2.Cells(Rows.Count, 1).End(xlUp).Row & "").Value: Let ArrMn = sht1.Range(sht1.Cells(1, 1), sht1.Cells(10, sht1.Cells(1, Columns.Count).End(xlToLeft).Column)).Value
Dim ArrResults() As Variant: ReDim ArrResults(1 To 1, 1 To UBound(ArrMn, 2)) 'Output Array dimensioned to a Row of exact Heading column length
    For NwRw = 1 To UBound(ArrFd, 1)  'Go down the "Rows" within captured Array
        For MnCm = 1 To UBound(ArrMn, 2) 'Go along the "columns" in the Main sheet Captured Array (Heading Array)
            For HdNu = 1 To 10 'Go through heading Spelling Possibilities in the Main sheet Captured Array (Heading Array)
                If ArrFd(NwRw, 1) <> "" Then  'Check to avoid looking for matching empty Look up values with empty headings, (of which there may be many !!)
                    If ArrFd(NwRw, 1) = ArrMn(HdNu, MnCm) Then 'Heading match conditions so:...
                    Let ArrResults(1, MnCm) = ArrFd(NwRw, 2) '...Write in Appropriate Name or Nutrition Value in output Array
                    Else 'No Heading Match condition, so do Nothing
                    End If
                Else 'No Entry in Output Array
                End If
            Next HdNu
        Next MnCm
    Next NwRw

 Let ActiveCell.Resize(1, UBound(ArrResults, 2)).Value = ArrResults() 'Nice VBA allowed "One Liner" to output results. Resize Active Cell to size of the output Array, then assign the Output Array to this range

End Sub 'SimpleArraySort_1_ExcelForum

…………………………………………………………………………………….

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.