Me again, I have used the R1C1 function on reccomendation and that
solved much of the above problem. The only thing I am stumped on now is
how to remove the ' ' from the line below. I left the cell reference
for the vlookup as a2:b43 as it is on another sheet and i didnt put th
$ in as i thought it didnt need it (and also it didnt like the $!) but
it puts the formula in as 'A2':'B43' which returns a VALUE#

ActiveCell.Offset(0, 1).range("A1").Select
ActiveCell.Formula =
"=IF(R[0]C[-2]=ISBLANK(TRUE),1,VLOOKUP(VALUE(MID(R[0]C[-2],12,2)),Sheet2!A2:B43,2))"

How would I get the vlookup to remain as a static reference to sheet 2?

Duncan