VBA Error handling stops working always on the same product (in this example)

[MartinL]

I have a vlookup in my code with some error handling which for some reason suddenly does not work as expected, that is the error handler does not do it job and excel takes over why?

Always on widget 6 as in this example

My data

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Code	Date	W	Company_id	Vat ID	Customer key	Customer name	ProductID	Product description	QTY	Sales amount	Currency	NIV
2	155156	20160111	W	FI	FI07052241	10713485	MNTYHARJUN KONE-SHK KY (TEKNISET	C13T07154010	Widget 1	1	33.91	EUR *	NIV
3	155156	20160111	W	FI	FI07530872	10713116	DATA-KOIVISTO OY	C11CE27401	Widget 2	2	3646	EUR *	NIV
4	155156	20160111	W	FI	FI07530872	10713116	DATA-KOIVISTO OY	SESHQ2329	Widget 3	2	0.02	EUR *	NIV
5	155156	20160111	W	FI	FI02046870	10713494	TURUN TIETOKESKUS OY	C13T18164010	Widget 4	2	95.4	EUR *	NIV
6	155156	20160111	W	FI	FI02046870	10713494	TURUN TIETOKESKUS OY	C13T79014010	Widget 5	2	54.6	EUR *	NIV
7	155156	20160111	W	FI	FI09247747	10713510	PCP PARTNER OY	C13T694200	Widget 6	4	789.6	EUR *	NIV
Sheet1

My lookup table

Excel Workbook
	A	B
1	ProductID	Barcode
2	C13T07134021	8715946495484
3	C13T07134011	8715946495453
4	C13T07154020	8715946364063
5	C13T07154010	8715946361246
6	C13T07114011	8715946495439
7	C13T07114021	8715946495460
8	C13T18014010	8715946517926
9	C13T18014020	8715946517933
10	C13T07144011	8715946495545
11	C13T07144021	8715946495491
12	C13T18024010	8715946518022
13	C13T18024020	8715946518039
14	C13T18034010	8715946518046
15	C13T18034020	8715946518053
16	C13T18044010	8715946518060
17	C13T07124011	8715946495446
18	C13T07124021	8715946495477
19	C13T18044020	8715946518077
20	C13T18064510	8715946542560
21	C13T18064020	8715946518176
22	C13T18064010	8715946518169
Lookup

My Vba

Sub ML14Jan2016()
On Error GoTo MyErrorHandler:

Dim Ray As Variant, Ac As Long, Rw As Long, c As Long, Throw As String, Catch As Long, CatchList As String

Ray = ActiveSheet.Range("A1").CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2), 1 To 10)
    nray(1, 1) = "Year": nray(1, 2) = "Week": nray(1, 3) = "StoreNm"
    nray(1, 4) = "StoreNo": nray(1, 5) = "Description": nray(1, 6) = "UID"
    nray(1, 7) = "Volume": nray(1, 8) = "Stock": nray(1, 9) = "Chain": nray(1, 10) = "Country":

c = 1

    For Rw = 2 To UBound(Ray, 1)

    ' Check UID is valid
    If (Left(Ray(Rw, 8), 4)) = "C13T" Then
    
        'If this lookup fails it should go to the error handler
        Throw = Application.WorksheetFunction.VLookup(Ray(Rw, 8), Sheets("Lookup").Range("A2:A22"), 1, False)
        
        c = c + 1

        nray(c, 1) = Left(Ray(Rw, 2), 4) + 0 'Year
        nray(c, 2) = DatePart("ww", DateSerial(Left(Ray(Rw, 2), 4), Mid(Ray(Rw, 2), 5, 2), Right(Ray(Rw, 2), 2) - 2)) 'Week
        nray(c, 3) = Ray(Rw, 7)      'StoreNm
        nray(c, 4) = Ray(Rw, 6)      'StoreNo
        nray(c, 5) = Ray(Rw, 9)      'Description
        nray(c, 6) = Application.WorksheetFunction.VLookup(Ray(Rw, 8), Sheets("Lookup").Range("A2:A22"), 2, False)       'UID
        nray(c, 7) = Ray(Rw, 10)     'Volume
        nray(c, 8) = 0               'Stock
        nray(c, 9) = "Prisma"
        nray(c, 10) = "FI"

MyErrorHandler:
If Err.Number = 1004 Then
    MsgBox "Inks Not found :" & Ray(Rw, 8)
    Catch = Catch + 1                                'Count of records not found
    CatchList = CatchList & vbNewLine & Throw        'List of product IDs not found
    Err.Number = 0                                   'reset error number
End If

    End If
    Next Rw

Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Mysheet" 'Change name to suit

With ActiveSheet.Range("A1").Resize(c, 10)
     .Value = nray
     .Borders.Weight = 2
End With

If Catch > 0 Then MsgBox "Inks Not found :" & vbNewLine & CatchList

End Sub

Can anyone see whats wrong

 

[RoryA]

Yes - you don't have a Resume statement to reset the exception. (See this page: On Error WTF? | Excel Matters)

 

[MartinL]

Yes - you don't have a Resume statement to reset the exception. (See this page: On Error WTF? | Excel Matters)

Thanks Rory