VLOOKUP provides 2042 error

[jacko2401]

I have 3 worksheets:
1. "Main": This is the output worksheet I will be working from
2. "Data": This has the underlying data worksheet
3. "Sheet2": This is seperate data that I want to check if it also appears in the "data" worksheet

The "Data" worksheet has column A with a list of unique names.
The "Sheet2" worksheet also has column A with a list of names where some of those names appear in the column A of Data worksheet.

As a starting point, I want to produce VBA code that will populate a column in the "Main" worksheet with all the names that appear in Sheet2 as well as Data. I've tried a looped VLOOKUP but it eventually comes back with a Error 2042 when a #N/A is produced.

Please can someone let me know if I am using the right statement that can be tweaked, the wrong stement or suggest a way tp make my coding more robust. Here is what I have so far:

'Find the last row of the sheet2 list of names so I know how many vlookup statements to make
Sheets("Sheet2").Select
Sheets("Sheet2").Range("A1").End(xlDown).Select
LastRow = ActiveCell.Row

'Selects the first cell in the main worksheet
Sheets("main").Select
Range("A1").Select

'completes a VLookup from sheet2 to search for data in data worksheet until reaches the last row of the data in Sheet2

Do
ActiveCell.FormulaR1C1 = "=VLOOKUP('Sheet2'!RC[1],Data!C[2]:C[9],1,FALSE)"
ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell = ("A" & LastRow)