Odd error message when evaluating formula

  • Thread starter Thread starter Dkline
  • Start date Start date
D

Dkline

We run a macro that queries an Access file.

The values returned from the query are placed starting in Cell A2. Colmn "A"
has a numeric value coming out of the database. We set the Selection.Style
to "comma" for column "A".

A formula is entered in Column "D" for each row that the query returns.
Formula is:
ActiveCell.FormulaR1C1 =
"=IF(RC[1]=""Interest"",""Interest"",IF(RC[-3]<0,""Sale"",""Purchase""))"

This was working last week. Now today we get Column D as a line of text
rather than as the results of the formula. So now in the cell you see:
=IF(RC[1]=""Interest"",""Interest"",IF(RC[-3]<0,""Sale"",""Purchase""))

If I F2 the call to put it into Edit mode and then hit F9 to calculate it,
it calculates correctly and overwrites the formula with the calculated
result.

If I try to evaluate it in the Formula Auditor I get the message "The cell
being currently evaluated contains a constant."

When I go to the Edit menu, select GoTo, select the "Special" button, and
select "Constants", it lights up the entire area returned by the query -
including column "A" which is supposed to be numeric values.

Excel version is 2003.

Where did we go wrong between Friday and today?
 
Maybe the cell was formatted as text?

With ActiveCell
.numberformat = "General"
.FormulaR1C1 = _
"=IF(RC[1]=""Interest"",""Interest"",IF(RC[-3]<0,""Sale"",""Purchase""))"
end with


We run a macro that queries an Access file.

The values returned from the query are placed starting in Cell A2. Colmn "A"
has a numeric value coming out of the database. We set the Selection.Style
to "comma" for column "A".

A formula is entered in Column "D" for each row that the query returns.
Formula is:
ActiveCell.FormulaR1C1 =
"=IF(RC[1]=""Interest"",""Interest"",IF(RC[-3]<0,""Sale"",""Purchase""))"

This was working last week. Now today we get Column D as a line of text
rather than as the results of the formula. So now in the cell you see:
=IF(RC[1]=""Interest"",""Interest"",IF(RC[-3]<0,""Sale"",""Purchase""))

If I F2 the call to put it into Edit mode and then hit F9 to calculate it,
it calculates correctly and overwrites the formula with the calculated
result.

If I try to evaluate it in the Formula Auditor I get the message "The cell
being currently evaluated contains a constant."

When I go to the Edit menu, select GoTo, select the "Special" button, and
select "Constants", it lights up the entire area returned by the query -
including column "A" which is supposed to be numeric values.

Excel version is 2003.

Where did we go wrong between Friday and today?
Click to expand...
 
Back
Top