
I am trying to add formulas to a range.
I have two sheets 'sheet1' and 'sheet2'.
I need to set sheet2's formula like this:
sheet2's A1 = sheet1! A1 * 10
sheet2's B1 = sheet1! B1 * 10
...
sheet2's E1 = sheet1! E1 * 10
But result is :
sheet2's A1 = sheet1! A1 * 10
sheet2's B1 = sheet1! A1 * 10
...
sheet2's E1 = sheet1! A1 * 10
My code:
ExcelWorksheet sheet2 = workbook.Worksheets["sheet2"];
sheet2.Cells["A1:E1"].Formula = "sheet1!A1 * 10";
could some one help.


Developer
Nov 13, 2014 at 5:18 AM

Hi,
To achieve this you must set the formulas cell by cell.


Nov 13, 2014 at 8:52 AM
Edited Nov 13, 2014 at 8:57 AM

Hi, swmal
Thanks for your response.
Because the formula is dynamic, at last I use below code:
//in formula, sheet1! > [sheet1]
string formula = "[sheet1]A1 * 10";
//change "[sheet1]" to "$AAA1*"
Regex reg = new Regex(@"\[[^\[]*\]");
MatchCollection mc = reg.Matches(formula);
foreach (Match m in mc)
{
string v = m.Value;
formula = formula.Replace(v, "$AAA" ＋ (m.Index ＋ 1) ＋ "*");
}
//set range's formula
ExcelRange range = sheet2.Cells["A1:E1"];
range.Formula = formula;
//change "AAA1*" to "sheet1!"
foreach (ExcelRangeBase cell in range)
{
string cellFormula = cell.Formula;
foreach (Match m in mc)
{
string v = m.Value;
cellFormula = cellFormula.Replace("$AAA" ＋ (m.Index ＋ 1) ＋ "*", v.Replace("[", "").Replace(']', '!'));
}
cell.Formula = cellFormula;
}

