Incrementing a formula in an array

English
, ,
#1

Using setarrayformula as follows:

oRange = aSheet.getCellRangeByName(C13:C209)
oRange.setArrayFormula(=IF(OR(B13=0;ISFORMULA(D13));0;B13*D13*-1))

The formula is strictly copied over the range of cells without incrementing. For example, the formula in C14 should be

=IF(OR(B14=0;ISFORMULA(D14));0;B14D14-1)

Is there a way to get setArrayFormula (or some other command) to increment as it would if it was copied to the first cell and expanded over the range by dragging down the cell corner to the last cell at C209?

Thanks.

#2 
cell = aSheet.getCellByPosition(2,12)
cell.setFormula("=some_valid_expression_in_english_notation_with_semicolons")
rg = aSheet.getCellRangeByPosition(2,12,2,208)
rg.fillSeries(com.sun.star.sheet.FillDirection.TO_BOTTOM,com.sun.star.sheet.FillMode.SIMPLE,0,0,0)
1 Like
#3

LibreOffice: XCellSeries Interface Reference

#4

Your suggestion results in the same difficulty. The row number does not increment by 1, nor should it iff FillMode is SIMPLE. I unfortunately cannot fine another FillMode which does what I want:

Cell C13
IF(OR(B13=0;ISFORMULA(D13));0;B13*D13*-1
Cell C14
IF(OR(B14=0;ISFORMULA(D14));0;B14*D14*-1

and so on

I was looking for a non-dispatcher way of doing what the below does which I can call after putting the formula into the R1 cell.

Sub FillRows(R1,R2) ’ Use a formula to fill rows R1 to R2
’ Currently selected cell is C1,R1 & the formula has been written to that cell
’ R2 is the last row to be filled
dim args1(0) as new com.sun.star.beans.PropertyValue
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)
args1(0).Name = “ToPoint”: args1(0).Value = R1
dispatcher.executeDispatch(oBook, “.uno:GoToCell”, “”, 0, args1())
args1(0).Name = “EndCell” : args1(0).Value = R2
dispatcher.executeDispatch(oBook, “.uno:AutoFill”, “”, 0, args1())
args1(0).Name = “ToPoint” : args1(0).Value = R1 & “:” & R2
dispatcher.executeDispatch(oBook, “.uno:GoToCell”, “”, 0, args1())
End Sub

What makes the Sub work is the uno:AutoFill

#5

There are fundamental misunderstandings.
The OQer needs to understand that doing manually what he did (when recording his macro) did not create an array formula, but a series of cell formulas.
@fpy tried to point to that fact.
Already the subject tells us that the question can’t be taken literally: Formulas don’t get incremented, but the references they contain are adapted or not depending on the absolute/relative notation.
For further studies see attachment. Study the contained Sub first.
disask_135225_strangeFilling.ods (11.8 KB)

#6

What are you trying to tell me? I’m doing this shit since 24 years.
ask135225.ods (16.9 KB)

#7

Of whom do you talk?

#8

This is what I mean:

#9

I have never used the SetArrayFormula, so I may be out of my league here. Still, according to the documentation I can find, you need to specify the formula with this method exactly like when you type an array formula manually.

For an array formula entered manually, every element in the formula which is supposed to reference a corresponding array location, needs to specify the entire vector/array range.

Assuming that I correctly understand the purpose of your formula (at least in technical terms), I would try:

oRange = aSheet.getCellRangeByName(C13:C209)
oRange.setArrayFormula(=IF(OR(B13:B209=0;ISFORMULA(D13:D209));0;B13:B209*D13:D209*-1))

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.