How to use VLOOKUP in Excel VBA to find matching cells

I am just starting out with Excel VBA and to be honest I am not that skilled in using normal Excel either.

I've got one sheet that has unique codes in column C, I also have another sheet that has unique codes in column A (except first rows as they've got column labels).

In case this unique code from sheet 1 is also found in the column in sheet 2, I want column J in the sheet1 to have that code value, otherwise, if it cannot be found, I want it to have #N/A.

Normally in Excel I would do this by selecting J2 and entering following function:

=VLOOKUP(C2,Sheet2!A:A,1,False)

Then I'd just double click the corner of the cell to populate all rows.

How do I do it in VBA? I wrote this code hoping it would do something:

Worksheets("Sheet1").Activate
ActiveSheet.Range("J:J").Value = Application.VLookup(C2,Worksheets("Sheet2").Range("A:A"),1,False)

However, this does not work. I just get #N/A for all cells in the J column. Any suggestions what I can do?

Content Attribution

This content was originally published by Marcin at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.