Applying R1C1 formula to a range

Let’s say I have an Excel sheet with the following column headings in the first Row:

DataA, DataB, DataC, TotalAtoC, Percent, PctTimesA, PctTimesB, PctTimesC
Where the TotalAtoC columns contains the Sum of columns DataA and DataB and DataC
And the PctTimesA equals column DataA time Percent and similarly for PctTimesB and PctTimesC

These are perfect situations for using relative formulas in the R1C1 format.
The formula for the TotalAtoC column would be "=SUM(RC[-3]:RC[-1])"
The formula for the PctTimesA, PctTimesB and PctTimesC columns would all be "=RC[-5]*RC5"

I can define two ranges; one for the TotalAtoC column and one for the PctTimes columns as follows.
Range totalAtoC_Range = sheet.Cells.CreateRange(1,3,sheet.Cells.MaxDataRow,1);
Range pctTimes_Range = sheet.Cells.CreateRange(1,6,sheet.Cells.MaxDataRow,3);

I would like to apply the formula to the entire range and do something like:
totalAtoC_Range.FormulaR1C1 = “=SUM(RC[-3]:RC[-1])”;
pctTimes_Range.FormulaR1C1 = "=RC[-5]*RC5"

But that is not possible.
I tried putting the formula in the Value of the range like this,
totalAtoC_Range.Value = “=SUM(RC[-3]:RC[-1])”;
and the value did get set for all the cells, but the formula would not get calculated when I open the Excel file.

I would prefer to not have to set the formula in each individual cell in the range.

Is there any way I can apply a formula to a range or to all the cells in the range with one statement?

Thanks.

I just realized that there isn’t even an easy way to iterate through all the cells of a range.

And there also isn’t a way to get the Cells collection of all the cells of a range.

So I ended up writing a routine that uses nested “for” loops to assign the formula to each cell in the range.

///
/// void AssignR1C1FormulaToRange - in Aspose assign the R1C1 formula to all cells in the range
///
/// Range - the range that needs the formula assigned
/// string - the formula as a string like “=SUM(RC[-13]:RC[-2])”
private void AssignR1C1FormulaToRange(Range range, string formula)
{
int columnCount = range.ColumnCount;
int rowCount = range.RowCount;
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
for (int colIndex = 0; colIndex < columnCount; colIndex++)
{
range.GetCellOrNull(rowIndex, colIndex).R1C1Formula = formula;
}
}
return;
}

Please tell me there is an easier way to do this.

Thanks.

Correction. There is a way to use foreach on a range to get each individual cell in a range, so things are simplified a little bit.


private void AssignR1C1FormulaToRange(Range range, string formula)
{
foreach ( Cell c in range )
{
if (c != null)
{
c.R1C1Formula = formula;
}
}
return;
}

However, it still would be nice to be able to apply the formula to the whole range at once.

Hi,


Well, I am not sure if there is any more efficient and direct approach to apply a formula to the cells in a range. By the way, we think you may try to use either SharedFormula or ArrayFormula to specify to the cells accordingly. For example, you might need to get following result in the similar format:
e.g
cell D1: = SUM(A1:C1)
cell D2: = SUM(A2:C2)
We think, you may try to use the sample codes such as, cells[“D1”].SetSharedFormula(“=SUM(A1:C1)”, cells.MaxDataRow+1, 1) that may fit your requirements although we are not completely sure about it if it fits in your situation properly. We also recommend you to kindly see the document/ article for your reference:

Thank you.