Stretch fromula

FLX TMS FlexCel for VCL & FMX
1

Hi!

I need help:-)

I want stretch formula in column with FlexCel 5.6.9.

For example,  in Cells [aRow, aCol] contains formula: "=A1+B1". After stretching should be able to:

Cells [aRow, aCol]  contains formula: "=A1+B1"

 Cells [aRow +1, aCol +1] contains formula: "=A2+B2"   

  Cells [aRow +2, aCol +2] contains formula: "=A3+B3"    

..............

  Cells [aRow +n, aCol +n] contains formula: "=A(n-1)+B(n-1)"    

It is possible? If it is possible than how a can do it?

2

Hi,


To stretch the formula in aRow, aCol n times down, you would do something like this:

  xls.InsertAndCopyRange(TXlsCellRange.Create(aRow, aCol, aRow, aCol), aRow + 1, aCol, n, TFlxInsertMode.NoneDown);

To stretch it to the right, you would do:
  xls.InsertAndCopyRange(TXlsCellRange.Create(aRow, aCol, aRow, aCol), aRow, aCol + 1, n, TFlxInsertMode.NoneRight);

And if you want to do it in both ways, you first strectch it to the right, then stretch the whole row down.

Note: This is for formulas that are already in your file and you need to copy. If you are entering the formulas and don't want to manually modify the text so it reads A3, A4... etc, then the you might use R1C1 notation:

xls.FormulaReferenceStyle := TReferenceStyle.R1C1;
for row := 2 to 5 do
   for col := 2 to 6 do
       xls.SetCellValue(row, col, TFormula.Create('=R[-1]C[-1]+R[-1]C'));

Regards,
   Adrian.

3

Hi!

It works!!!!!!

Thank you very much!!!!