Gavin,

It's hard to find the code -- for the sake of others it's the final line in the Sheet object Change event, correct ?

Before getting on to the question...

-- you will find naming your Modules makes your life easier in the long run (set via Properties window (F4))
-- remove Modules you don't need (there are lots of empty modules)

To the question...

In formula terms:

=IF(F6="","",(if(D6="A","A program",(if(and(D6="B",F6<16,"B Thin",(if(and(D6="B",F6>16,"B Thick",LOOKUP(ROUNDUP((F6/(LOOKUP(D6,'Calc Sheet'!F:F,'Calc Sheet'!H:H))),0),'Calc Sheet'!$K$1:$K$44,'Calc Sheet'!$J$1:$J$44))))))))))
Should be (I think)

=IF(F6="",IF(D6="A","A program",IF(D6="B","B "&IF(F6<16,"Thin","Thick"),LOOKUP(ROUNDUP(F6/LOOKUP(D6,'Calc Sheet'!F:F,'Calc Sheet'!H:H),0),'Calc Sheet'!$K$1:$K$44,'Calc Sheet'!$J$1:$J$44))))
You seem to know what you're doing in terms of R1C1 so I have no doubt you can adapt the above to fit in with the relative positioning (based on column F).