Hi,

The first thing that comes to mind is that you are using an Analysis ToolPak
function which will return #NAME! errors if the ATP is not attached.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Harold Shea" wrote:

Hi, Charles:

As you surmised, there are three formulas, each with a different name. Here
they are, with their names in front:

ED_BEGIN_DATE: =IF(OR('Master List'!$M1<"Y",'Master
List'!$F1=0),"",WORKDAY('Master List'!$F1,-50,'MGH Holiday
Calendar'!$B$2:$B$25))

ED_RELEASE_DATE: =IF(OR('Master List'!$M2<"Y",'Master
List'!$F2=0),"",WORKDAY('Master List'!$F2,-40,'MGH Holiday
Calendar'!$B$2:$B$25))

PROJECTED_FRD: IF(OR('Master List'!$M3<"Y",'Master
List'!$F3=0),"",WORKDAY('Master List'!$F3,-30,'MGH Holiday
Calendar'!$B$2:$B$25))

Both the formulas themselves and their named counterparts work fine in the
sheet itself, and most of the time, when the macro inserts the name into
cell(s), it continues to work fine--most of the time. But then there are the
apparently patternless exceptions I described. It's freaking me out. I'm just
wondering why, when the macro insertion reults in NAME? appearing in the
cell, it goes away when I enter and exit editiing mode in the cell without
actually changing anything.

"Charles Williams" wrote:

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.