@Twifoo,
thanks for the suggestion though i am a little confused how to wrap the formula with an IF type of statement. My formula is shown below
Range("C3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],ProdOver!C[-2]:C[2],2,FALSE)"
Selection.AutoFill Destination:=Range("C3:C150"), Type:=xlFillDefault
but if i wrap it in an IF statement wouldn't that conflict with the VLOOKUP? To have the IF statement return a blank cell it needs the #N/A response from the VLOOKUP but then will delete the formula - or what am i missisng?