Vlookip to VBA Script

May be this..

Sub Macro1()
    Range("C2").Formula= "=VLOOKUP(""*""&A2&""*"",Sheet2!B:B,1,0)"
    Range("C2").select
    Set DesRange=Range("C2:"& Cells(Cells(Rows.count,"A").end(xlup).Row,"C").address)
    Selection.AutoFill Destination:=DesRange, Type:=xlFillDefault    
End Sub

A Sample workbook with non sensitive data would be helpful....