Hello
Thank you, the first option (to add the line of code) worked perfectly.
I then tried the second option as you say would be the better option,
but unfortunately it returns a Compile/Syntax error on the 3rd line.

If you could get the code to work, that would be great, (if it is the better
way to do it), but your first option is working, so thanks once again.

Cheers
Ian


"MSP77079 " wrote in message ...
Two options.

One option is to leave the macro pretty much as is. After you stuff
the formula in the cell, add this line of code:

Range("C" & Target.Row).Value = Range("C" & Target.Row).Value

that will replace the formula with the result.


A better option is to replace the line that reads
With Range("C" & Target.Row)
Formula = "=IF($A" & Target.Row & "="""","""",VLOOKUP($A" & _
Target.Row & "," & "PARTNUMBERS!$A$2:$B$1000,2,FALSE))"

with this:
myResult = application.WorksheetFunction.VLookup($A" & _
Target.Row & "," & "PARTNUMBERS!$A$2:$B$1000,2,FALSE)
Range("C" & Target.Row) = myResult


---
Message posted from http://www.ExcelForum.com/