Hi Harold,

Whats in the Named Formulas?

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Harold Shea" wrote in message
...

I have a formula to which I have assigned a name in Insert Name
Create--let's say "FormulaX". When I type "=FormulaX" into a cell on the
worksheet, it works fine.

I use the name in some Worksheet_Change code as well, such that, under
certain conditions, the formula is input into a cell. The relevant part of
the code is as follows (I've omitted the stuff at the top and bottom):

...

For Each myCell In myRng.Cells
With myCell
If (Me.Cells(.Row, "E").Value = "TENTATIVE" Or Me.Cells(.Row, "E").Value =
"Tentative" Or Me.Cells(.Row, "E").Value = "tentative") And
(Me.Cells(.Row,
"M").Value = "Y" Or Me.Cells(.Row, "M").Value = "y") Then Me.Cells(.Row,
"O").Formula = "=ED_BEGIN_DATE": Me.Cells(.Row, "P").Formula =
"=ED_RELEASE_DATE": Me.Cells(.Row, "Q").Formula = "=PROJECTED_FRD"
End With
Next myCell

...

(I haven't put any line breaks into the "If" statement yet, so its on one
really long line in the module; the breaks you see here are just due to
lazy
copy/paste.)

This works quite well most of the time--when the code deposits the formula
name into a cell, it picks up data and returns the correct value.
SOMETIMES,
though, the cell it's entered into shows "NAME?" instead. I cannot figure
out
the pattern of when it does and when it doesn't. When it does happen,
though,
I can double-click on the cell itself, and without changing anything at
all,
exit it and the cdll now returns a value instead of an error message.
Also,
if I do a "Save As" under a new file name on a workbook that contains
these
bogus "NAME?" messages at the time, they clear up in the course of saving
and
start returning actual values. (I've tried forcing a calculation on a
sheet
with the bogus name values, and that doesn't clear it up.)

Does anyone have any ideas on how to resolve this? I suppose I could try
putting the formula itself right into the code, but then it won't be as
easy
for users to edit, so I'd like to avpid that if possible. It's just weird
that it works most of the time.