Originally Posted by
offthelip
the With range statement means the code accesses the spreadsheet multiple times
This is not true.
Originally Posted by
offthelip
the basic principle of minimising the number of accesses to the spreadsheet is something I have used many times to speed up macros.
That is true, however the original code gains access only once to the sheet with:
.Formula = "=IF(OR($AF59="""",$AF59="""",$AJ59=""""),0,IF(AY$58<$AJ59,0,IF(AY$58>=$AJ59,IF(0<($AN59),MIN(($AN59-SUM(AX59:$AX59)),$AO59,SUMIF(Shadow_Dept_Lines_Sum_Col,$AM59,AY$4:AY$56)-SUMIF($AM$58:$AM58,$AM59,AY$58))))))"
This line:
Range("AY59:EX" & lLR).Formula = inarr
is no more efficient than this line:
.Formula = "=IF(OR($AF59="""",$AF59="""",$AJ59=""""),0,IF(AY$58<$AJ59,0,IF(AY$58>=$AJ59,IF(0<($AN59),MIN(($AN59-SUM(AX59:$AX59)),$AO59,SUMIF(Shadow_Dept_Lines_Sum_Col,$AM59,AY$4:AY$56)-SUMIF($AM$58:$AM58,$AM59,AY$58))))))"
VISHAL120, I suspect strongly there is a circular reference on the sheet, only it takes some looking to find out where it is as Excel doesn't seem to report it (probably to do with the UDFs). I will look again when I get time (your formulae are many and long (and not always correct!)) and I'm not 100% sure what you intend with the formulae.
Reply With Quote