We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

problem setting the formula property of a cell

Thread ID:





98556 Mar 8,2011 05:38 PM Mar 10,2011 10:56 PM ASP.NET Web Forms (Classic) 5
Tags: XlsIO
Asked On March 8, 2011 05:38 PM

I've got a formula I can paste into an Excel cell with no problem but that gives me an error when I try to set the .Formula property of a cell:

No Expression foundUnexpected token type: EndOfFormula, string value: at position 8. Formula: "H2:H13", Position: 8

The formula I'm using (and I can recreate this in the WorksheetManipulations.aspx.cs page of the sample application you provided) is

sheet.Range["A50"].Formula = "=SUM(COUNTIF(INDIRECT({\"H2:H13\" }), \"> 0\"))";

Obviously, there's a problem with escaping the quotation marks, so do you have a work around for this issue? Thanks.


Sridhar [Syncfusion]
Replied On March 9, 2011 06:32 AM

Hi Amanda,

The issue with the usage of the formula you have given.
We have given below the correct formula and does not produce any issue.

Modified source:

sheet .Range ["H20"].Formula ="SUM(COUNTIF(INDIRECT(\"H2:H12\"),\">0\"))";

Please, check he syntax of the formula with MSExcel then you can easily apply with XlsIO.

Please let me know if you require any calrifications.


Replied On March 9, 2011 10:54 AM

Actually, the formula I sent to you is correct, and those braces are necessary when there are multiple ranges sent to the INDIRECT function. I have tried setting the .Formula property with the following function, and it fails:

"=SUM(COUNTIF(INDIRECT({\"H2:H5\", \"H7:H13\" }), \"> 0\"))"

In Excel, both
=SUM(COUNTIF(INDIRECT({"H2:H13"}), "> 0"))
=SUM(COUNTIF(INDIRECT({"H2:H5","H7:H13" }), "> 0"))"

work correctly. While the braces are optional in the first, they are required in the second. So they are required in my code because I deal with multiple ranges in that INDIRECT function.

I apologize for not giving you the more complex formula in my original post, but I need a solution that maintains the use of the curly braces. Please let me know if you have a way to work around this issue.

Sridhar [Syncfusion]
Replied On March 10, 2011 05:39 AM

Hi Amanda,

We have created the sample at our side for the given formulas and attached here.And the inbuilt function, curly braces, multiple ranges are working correctly here.

These are all the Formulas working fine with my sample.

sheet.Range["K13"].Formula = "SUM(COUNTIF(INDIRECT({\"E1:E5\",\"E7:E10\"}), \">0\"))";
sheet .Range ["J13"].Formula ="SUM(COUNTIF(INDIRECT({\"E2:E13\"}), \">0\"))";

Please try the sample at your side and let us know if this helps you.

Please let us know if you require any further clarifications.

Dont leave any spaces between the characters because we are passing the formula as an string value it also takes the spaces between them.



Replied On March 10, 2011 11:46 AM

Thanks, it was a space that was catching me out. Works great now.

Sridhar [Syncfusion]
Replied On March 10, 2011 10:56 PM

Hi Amanda,

Thank you for using Syncfusion products.

Thnaks for the update.

We are very happy to help you out in case of any issues.

Please let us know if you require any clarifications.



This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.