Add formula to all cells in a range

Aspose.Cells Product Family
1

I have a formula that I need to add to all cells in a Range where the range is more than 1 cell.

I know you don't support VBA but this is how it is done in VBA and I need to replicate this with Aspose and c#.

Range("ProjMktShare").Select
Selection.FormulaArray = sFormula
Selection.Locked = True

2

Hi,

Kindly create a string array and store all your formulas in it, Use Cells.ImportFormulaArray() method to import the array to the cells.

Thank you.

3

Hi,

I need to insert the formula documented here: http://support.microsoft.com/kb/136738/en-us

This formula to work properly you need to enter with “CTRL + SHIFT + ENTER”, otherwise it can not assess the range in the state.

I tried to enter the formula with the command ImportFormulaArray, but it is as if the formula were to be confirmed with the ENTER key instead of “CTRL + SHIFT + ENTER “.

This is my exact formula:
=SUM(IF(MOD(COLUMN(B14:AK14),3)=2,B14:AK14,0))

and this is how I entered in Aspose Cells:
sheet.Cells.ImportFormulaArray(New String() {”=SUM(IF(MOD(COLUMN(B14:AK14),3)=2,B14:AK14,0))”}, 13, 37)

Why is not working properly?

ps: my Aspose Cells version is 4.4.0.0

4

Hi,

I think you should use Cell.SetArrayFormula() API, see the document for your reference:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/using-formulasfunctions-to-process-data.html

Thank you.

5

Thanks, this method works correctly :wink: