evega evega - 1 month ago 5x
C# Question

Interop Excel Range Formula assignment with string concatenation

When try to assign a concatenated string to Range.Formula, doesn't works, but simple string do perfectly.

Working code:

string formula = string.Format("=Round1!D{0} + Round2!D{0}", cellId);
myRange.Formula = formula;

Failing code:

string formula = string.Format("=Round{0}!D{1}!", 1, cellId);
for (int i = 2; i <= numRounds; i++)
formula += string.Format(" + Round{0}!D{1}!", i, cellId);
myRange.Formula = formula;

¿WTF? xD

This makes imposible to generate the formula dinamicly

Thank you in advance.

string formula = string.Format("=Round{0}!D{2} + Round{1}!D{2}", 1, 2 cellId); 
myRange.Formula = formula;

If the sheets Round1 to Round3 (numRounds) are in order in the Workbook, then you can use 3-D reference. For example:

= Sum( Round1:Round3!D3 )

which will be the same as

= Round1!D3 + Round2!D3 + Round3!D3