Oops. I didn't notice this the first time:

ActiveCell.FormulaR1C1 = _
"=IF(R[0]C[-2]=ISBLANK(TRUE),1,VLOOKUP(VALUE(MID(R[0]C[-2],12,2))," & _
"Sheet2!A2:B43,2))"

is a mixture of both R1C1 and A1 reference style.

ActiveCell.FormulaR1C1 = _
"=IF(R[0]C[-2]=ISBLANK(TRUE),1,VLOOKUP(VALUE(MID(R[0]C[-2],12,2))," & _
"Sheet2!r2c1:r43c2,2))"

might work better.

(I was blinded with all the rc stuff that was at the beginning of the formula!)


Duncan wrote:

I did try changing it to activecell.formular1c1 = but that didnt work
either. I decided that I could manage without that line (the vlookup
was only declaring what type of document it was) and i decided that
sheet would be document specific which saved me the headache!

anyway I am posting to say thank you for your help.

Many thanks

Duncan

--

Dave Peterson