formula works when pasted, but not when typed
 

I have encountered a bizarre Excel 2003 bug that crops up from time to time.
Sometimes, a formula that is typed into a cell does absolutely nothing, i.e.,
Excel does not evaluate it. If I type the same formula into another cell and
then do a copy and paste, the formula works in the same location where it
previously failed to work. Here's the latest formula that I was using
where I encountered this problem.

=SUBTOTAL(103,$A$3:$A$64000)

I've verified that the cell format is "General" in both cases.

formula works when pasted, but not when typed
 

Most likely, the original cell is formatted for Text, which means that
Excel won't convert what you type to a formula. When you copy/paste,
Excel recognizes it as a formula and changes the format upon the
paste.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Fri, 17 Oct 2008 14:00:01 -0700, Phillip M. Feldman
wrote:

I have encountered a bizarre Excel 2003 bug that crops up from time to time.
Sometimes, a formula that is typed into a cell does absolutely nothing, i.e.,
Excel does not evaluate it. If I type the same formula into another cell and
then do a copy and paste, the formula works in the same location where it
previously failed to work. Here's the latest formula that I was using
where I encountered this problem.

=SUBTOTAL(103,$A$3:$A$64000)

I've verified that the cell format is "General" in both cases.

formula works when pasted, but not when typed
 

Your explanation is correct, but there's more to the story. Once one has
stored the formula in the cell, changing the format to numeric has no effect.
This is an Excel/VBA bug that I'd encountered years ago but forgotten about.
One must change the format to numeric _before_ writing the formula, as in the
following two statements:

ws.Cells(2, 1).NumberFormat = "0"
ws.Cells(2, 1).Formula = "=SUBTOTAL(103,$A$3:$A$64000)"

Thanks!