I have tried a few different codes and still can't get the vlookup to work. The code is not all my own work, some was suggested by "Sixthsense" and it works.

Cells(emptyRow, 7).Value = Evaluate("=VLOOKUP(F3,Sheet2!A:C,2,0)")
However this always references cell F3 and I want it to reference F4 and F5 as each entry is made into the spreadsheet using the userform.

I have also tried

Cells(emptyRow, 7).Value = TextBox2.Value = Application.VLookup(SapPartTextBox.Value, Sheets("Sheets2").Range("A1:C487"), 2, 0)
and

Cells(emptyRow, 7).Value = TextBox2.Value = ActiveCell.Formula.R1C1 = "=Vlookup(RC[1],sheet2!C:C[2],2,0,)"
I get either a #N/A or FALSE displayed.

Any ideas?

Regards

Tig