|Since I can't see your workbook from where I'm sitting, it's kind of hard for me to tell you why you aren't getting the results you expect.|
All my code does is put VLOOKUP formulas in a column, then Copy/Paste Values over them.
You could try this:
This is the same code with the sections that Paste Values and deletes Sheet 2 commented out.
When it's done, you'll be left with the formulas. Compare your formulas to mine and see what's different.
'Determine last row based on Column D
lastRw = Sheets(1).Range("D" & Rows.Count).End(xlUp).Row
'Format Column A range as General
Sheets(1).Range("A1:A" & lastRw).NumberFormat = "General"
'Insert formula using lastRw variable
Sheets(1).Range("A1:A" & lastRw).FormulaR1C1 = _
"=VLOOKUP(RC,Sheet2!RC:R[" & lastRw - 1 & "]C,3,0)"
' With Sheets(1).Range("A1:A" & lastRw)
' .PasteSpecial Paste:=xlPasteValues
' End With
''Delete Sheet2 without warning
' Application.DisplayAlerts = False
' Application.DisplayAlerts = True
Posting Tip: Before posting Data or VBA Code, read this How-To.