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.