Not a valid formula error

Not a valid formula error

Posted by: garrett on 31 July 2019, 5:25 pm EST

• 

  garrett

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 31 July 2019, 5:25 pm EST

  I have run into an error setting a simple formula to a cell. The error is
  
  Message=‘RC[-5]-RC[-1]’ is not a valid formula.
  
  Error offset: 0

  The line of code is:
  
  sht.Cells.Get(r, c).Formula = “RC[-5]-RC[-1]”

  The refernce style is set:
  
  sht.ReferenceStyle = Spread.Model.ReferenceStyle.R1C1

  Any ideas why this would be causing an exception?
  
  I’m using v12.45.20191.0 and Visual Studio 2017

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 1 August 2019, 12:19 am EST

  Hello,

  Please set the “ReferenceStyle” before the setting the formula. Like this

  
  sht.ReferenceStyle = Spread.Model.ReferenceStyle.R1C1
  sht.Cells.Get(r, c).Formula = "RC[-5]-RC[-1]"
  
  

  Hope it helps.

  Thanks.

• 

  garrett

  • Post Options:
  • Link
    Copy

  Posted 1 August 2019, 12:22 pm EST

  Sorry for the confusion. I am setting the reference style first, I should have made that clear.

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 2 August 2019, 1:59 am EST

  Hello,

  I have replied on the SupportOne case 392634. Could you please reply on S1 case to avoid confusion.

  Thanks.

• 

  garrett

  • Post Options:
  • Link
    Copy

  Posted 5 August 2019, 11:59 am EST

  Just in case anyone later runs into the same exception, the solution is to add the newly created sheet to the spread and then set the sheet’s reference style.