Modifying the formula of a cell

Posted by: tkmadan on 8 September 2017, 1:33 pm EST

  • Posted 8 September 2017, 1:33 pm EST

    <DIV class=ForumPostContentText id=_ctl0__ctl1_bcr__ctl0___PostRepeater__ctl2_PostViewWrapper>Setting cell values or cell formulas in the middle of a recalculation (e.g. inside the CutomFunction event) is not recommended.  If AutoCalculation is true (which is the default) then setting cell values or cell formulas will trigger a new recalculation cycle inside of the current recalculation cycle.  This could possibly lead to a deep (or even endless) recursive loop that eventually leads to a stack overflow. </DIV>
    <DIV class=ForumPostContentText> </DIV>
    <DIV class=ForumPostContentText>What is the solution for this problem, need to set any property or reset formula. In that case how to do it. Thanks</DIV>
  • Replied 8 September 2017, 1:33 pm EST

    Hello,


    If you need to change the Value of a dependent cell in the CustomFunction event that will cause the function to recalculate, you can set a global flag before setting the value and then reset it after setting the value. Then, in the CustomFunction event, check that the flag is not set before processing the code again.

  • Replied 8 September 2017, 1:33 pm EST

    Thanks for your reply. I am now able to sum the column and show on the last row based on the filter in Numeric Column and not the text Column.


    I have used SUMIF  to sum a particular column based on value I filter for the text column (COL_POS_ITEM_REF_SBB).<FONT size=2>


    </FONT><FONT color=#800000 size=2>"SUMIF("</FONT><FONT size=2> + fpStoreRequest.Sheets[0].Cells[0, COL_POS_ITEM_REF_SBB] + </FONT><FONT color=#800000 size=2>":"</FONT><FONT size=2> + fpStoreRequest.Sheets[0].Cells[(RowCount - 2), COL_POS_ITEM_REF_SBB] + </FONT><FONT color=#800000 size=2>","</FONT><FONT size=2> + </FONT><FONT color=#800000 size=2>"\"="</FONT><FONT size=2> + e.FilterString + </FONT><FONT color=#800000 size=2>"\""</FONT><FONT size=2> + </FONT><FONT color=#800000 size=2>","</FONT><FONT size=2> + fpStoreRequest.Sheets[0].Cells[0, COL_POS_SUPP_COST] + </FONT><FONT color=#800000 size=2>":"</FONT><FONT size=2> + fpStoreRequest.Sheets[0].Cells[(RowCount- 2), COL_POS_SUPP_COST] + </FONT><FONT color=#800000 size=2>")"</FONT><FONT size=2>;</FONT>


    <FONT size=2>Please find the  code snippet I use. Should I not use SUMIF? If so what to do.</FONT>


     


     


    <FONT size=2> 

    </FONT>

     

  • Replied 8 September 2017, 1:33 pm EST

    If we use Auto filter for two or more columns the Sum is to be calculated for a particular column. How is this possible. Please provide sample code.


    Thanks

  • Replied 8 September 2017, 1:33 pm EST

    Hello,


    There is not a way to implement this formula in Spread. You could create a CustomFunction that you use to write your own calculation. Or you can use the Change event to be notified of a Change and then programatically fill the last row with a calculated sum.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels