VLookup Not Posting Matching Results in Column

[BenChod]

Hello All -

I am using the following vlookup method in VBA to look up a value in the main table to a reference table in another worksheet. When there is match, copy that value in column A. For some reason, the copying is not working and I am hoping someone can tell me what I am doing wrong.

Sub VLookup()
On Error Resume Next
Dim table1 As Range
Dim table2 As Range
Dim cl As Range


Worksheets("QC").Activate
Range("A1").EntireColumn.Insert
Cells(1, 1) = "Env"


table1 = Worksheets("QC").Range("Y2:Y3000") ' Originating App from QC Table
table2 = Worksheets("ReferenceData").Range("A2:B20") ' Range of Reference Data Tabe
type_row = Worksheets("QC").Range("A2").Row ' Change A2 with the cell from where you need to start populating the Department
type_col = Worksheets("QC").Range("A2").Column




For Each cl In table1
  Worksheets("QC").Cells(type_row, type_col) = Application.WorksheetFunction.VLookup(cl, table2, 2, False)
  type_row = type_row + 1
  
Next cl


End Sub

[SamT]

It is my personal preference to avoid the use of Worksheet functions in VBA unless there is no other way

I am turning your loops inside out... instead of searching 19 Cells 2999 times, I will look at 1 column 19 times. Besides, I bet the biggest part of those 2999 cells are empty.

Since you aren't using "Option Explicit," I won't either. This time.

Note that this post was written without use of Excel, so there can be typos in this code.

Sub TryThis()

With Worksheets("QC")
.Columns(1).Insert
.Cells(1) = "Env"

For Each Cel in Worksheets("ReferenceData").Range("A2:A20")
Set Found = .Range("Y:Y").Find(Cel)
If Not Found Is Nothing then .Cells(Rows.Count, "A").End(xlUp).Offset(1) = Cel.Offset(,1)
Next
End With
End Sub

[BenChod]

Thank you for assisting. When I run your code, I received a compile error variable not defined for 'Cel'. I do have "Option Explicit", just didn't paste it. I removed the "Option Explicit" and the lookup is not giving me the right results. What's being copied to Col A is the first five entries of my reference table from my reference data worksheet.

As for my original code, the erorr I get is that 'cl' needs to be a variant or object and when I set this variable either as an 'Object' or 'Variant' it still doesn't work. This is what's frustrating me because I can't figure out why the vlookup is not working.

[BenChod]

Please disregard the issue. I copied the code and created another workbook, inserted a module and copied the code. When I did all this, the vlookup function worked. Don't know why, but it worked. Thanks to those you assisted.