How to generate whole table of values using code for VLookUp?|VBA & Macros|Excel Forum|My Online Training Hub

Hi!

Basically I'm trying to do something similar to that of Example 3 given in the below link: https://www.exceltrick.com/for.....es-in-vba/

The only difference is that the table (equivalent to 'Employee Table 1') I'm comparing to is on another worksheet. 

I used the code given in the link above but modified the code slightly to suit my purposes. 

Here is my code:

Sub Classify()

''copies over Product Type from hidden 'Product Codes' tab

''uses VLookup function

On Error Resume Next

Dim Pdt_Row As Long

Dim Pdt_Clm As Long

Sheets("P1").Select

finalrow = Cells(rows.Count, "A").End(xlUp).Row

Table1 = Worksheets("P1").Range("A14:A" & finalrow) ''Product Codes Column

Table2 = Worksheets("Product Codes").Range("A11:J397") ''Range of Product Types

Pdt_Row = Worksheets("P1").Range("E14").Row 

Pdt_Clm = Worksheets("P1").Range("E14").Column

For Each Cell In Table1

Worksheets("P1").Cells(Pdt_Row, Pdt_Clm) = Application.WorksheetFunction.VLookup(Cell, Table2, 10, False)

Pdt_Row = Pdt_Row + 1

Next Cell

End Sub

However, everytime I run the code, it will stop after a few lines. For example, say there are 100 product codes. The code will run for the first 20 product codes and identify the product name correctly for the first 20 codes. However, it stops there and does not continue.

I can't figure out what's the problem. 

Would appreciate some help here, thanks so much!