If formatting is text (as it must be) this can happen. Perhaps that's just coincidental due to some leftover formatting you didn't mean to keep - but if you continue to have this problem just set the formatting before writing in the formula.
I don't use Fill (for no particular reason) and or Select very often so my sample takes a slightly different approach:
This is untested - I hope it works for you. If not, I'll see if I can test this more rigorously at home with XL2007Code:Sub Foo() 'Insert New column N and write Formula Columns("N").Insert Range("N1").Formula = "Names" Range("N2").NumberFormat = "General" Range("N2").Formula = "=VLOOKUP(M2,'C:\Users\teatimecrumpet\Desktop\[_FINAL_Name_ITEMS_VBA.xlsx]UniQ-Names-Final'!$E$1:$G$51,3,FALSE)" '//Copy formula as far as values exist in adjacent column M Range("N2").Copy Range("N2:N" & Range("M65536").End(xlUp).Row).PasteSpecial End Sub