What should my calculation settings be?

[CondtllyFrmttd]

I have made a macro which FILLS DOWN formulas into an MS EXCEL 2000
Spreadsheet. My macro calculates the formulas except some have "
#NAME? " as a value.


The crazy thing about this is that when I go into the status bar and
hit enter the formula is calculatedthe exact number comes up!!!! I've
heard about Microsoft issues, is this another example?


What should my calculation settings be?


I'm not sure if manual, automatic, iteration, or calculate before
save will help me out.


Please help. Thanks

 

[Guest]

Normally if calculation is set to Automatic it will recalc after you fill
down a series of formulas.

Let me make a guess: Do the formulas in the cells that are filled down
contain any user defined formulas? If you copy a user formula to a new cell
it does not trigger the calculation, but if you edit the cell (as you are
doing when you go into it and press 'Enter') it will be calculated. It is a
common issue and not obvious until you have run into the problem and found
the solution - which is to put the statement Application.Volatile in the
function code.

Regardless of everything else, putting an Application.Calculate command in
your macro code will force a recalculation of everything in the workbook.

 

[CondtllyFrmttd]

my formulas are not user defined. the end result is only used to
provide a query or analysis later on. I'm really stumped, anyone have
any ideas???!?!?!?!?!?!?