Thread: formula works when pasted, but not when typed
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Phillip M. Feldman Phillip M. Feldman is offline
external usenet poster
  
Posts: 6
Default 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!
Reply With QuoteReply With Quote