problem setting the formula property of a cell

[NOLONGERWITHCOMPANYAmanda Kabak]

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.


amanda

[Sridhar]

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.

Thanks,
Sridhar.S

[NOLONGERWITHCOMPANYAmanda Kabak]

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"))
and
=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]

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.

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

Thanks,
Sridhar.S




F98556_a7979744.zip

[NOLONGERWITHCOMPANYAmanda Kabak]

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

[Sridhar]

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.

Thanks,
Sridhar.s