H
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.
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.