Named Formula Not Always Calculating

[Harold Shea]

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.

 

[Charles Williams]

Hi Harold,

Whats in the Named Formulas?

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

 

[Harold Shea]

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.

 

[Shane Devenshire]

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.

 

[Charles Williams]

The second thing that comes to mind is that you are using relative
references ($F2) in the name definitions.
This is tricky in defined names because the question becomes "relative to
what"?
Do you really mean to do this?

What are the name definitions in R1C1 mode?

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

 

[Harold Shea]

Sorry about the long delay in responding--luckily, I was out for the holiday.

Unluckily, I suppose, the ATP is turned on, which I guess kind of makes
sense, since probably the cells on the worksheet would always return an error
when they tried to swallow the WORKDAY function (instead of just sometimes)
if it weren't.

Thanks for the thought, though. Probably I'm just screwed.

 

[Harold Shea]

Actually, I hadn't thought of that at all. Yes, the relative reference in the
named formula is intentional: When "FIRM" or "TENTATIVE" is entered by the
user in a cell in Column E, and "Y" or "N" is entered in Column M, the macro
is should either enter the named formula, or "frrze" the cell at whatever
value it currently contains in Columns O, P, and Q.(The named formulas for
Columns O, P, and Q are different.) In the worksheet itself, the relative
named formulas work OK, but maybe there's something weird happening sometimes
when the macro deposits the same thing into a cell.

But the randomness of the thing still gets me. The NAME? reference only
appears about 10% of the time, and not in a pattern I can find. (At least,
not yet.) If the relative reference in the definition of the name were
choking up the macro, wouldn't it happen all the time.

Anyway, I can try rewriting the code in R1C1 style (which, frankly, I'll
have to find out how to do since I never really use it), but if I do that,
won't the formula be hard-wired into the macro so that users would have to
edit it in VBA if they wanted to, say, add or delete columns from the sheet?

The sheet is hopelessly complicated because of various user requests, but so
far I've been able to build it so users don't need to get into the VBA editor
to make worksheet-level alterations, but maybe that's not possible.

If you have any further ideas, I'd really appreciate it, but of course I
appreciate what you've suggested already. Sorry about the delay in response,
but I was out for the holiday weekend, which at this minute is already
seeming about ten years ago.

I'll check the site again in the morning. I think I'm going to get a
gin-and-tonic now.