Formula not working

[raghunaik]

I'm using the below to code to insert a formula in cell E1, what am i doing wrong here, please help

Please Login or Register  to view this content.

[DonkeyOte]

Not an ideal thread title but...

when using " in literal sense in VBA you must double-up, ie:

Please Login or Register  to view this content.

[Palmetto]

Why are you referencing the entire column?
VLOOKUP(E2,Roles!$B:$C,2,FALSE)

Excel 2007 has more than 1-million rows. It would be much more efficient to use a dynamic named range in the formula. The dynamic named range will expand down the column to account for new data.

[DonkeyOte]

That's an interesting one ... I agree in principle but wonder if given the fact VLOOKUP is in general a pretty efficient function limiting the range would make that great deal of difference (less so if used in TRUE mode and/or used range is small (ie limited recalc impact)) ... also it's worth remembering that named ranges can also impact performance if over used given they are evaluated when called (see: http://www.decisionmodels.com/calcsecretsb.htm)

My own interpretation based on my own (very) limited knowledge on this subject is that DNR's are especially useful when using very expensive formulae like CSE Arrays / Sumproduct and poss also when using the likes of INDEX and LOOKUP(2,1/(Boolean)) functions given these are also I'm led to believe quite slow... for most formulae I wouldn't use DNR's... there's also the age-old debate whether DNR's are good bad in general... I don't like them as I find it actually makes auditing more difficult but plenty have the complete opposite opinion to me - no change there then

[raghunaik]

thanks for all the replies, formula is working for me now
i think i should have used a better title.

thanks
~Raghu

[shg]

i think i should have used a better title.

Yes, you should. That's your one free bad title.