Paste formula in Libre Calc using Basic macro

Question

I am trying to replicate an excel macro in Libre Calc using the native Basic.

I've come up against what I feel should be a simple problem/resolution (for someone who has a clue what they are doing).

I want to copy the formula from 3 cells, say A1 to A3, and paste within the same sheet, in cells B1 to B3. I can get it to copy/paste the values, but not the formula.

Happy for the cells to be copied/pasted as an array, or individually via a loop. Whatever is simplest and works.

I have tried the following (note the cell references dont reflect A1:A3 / B1 to B3, but my actual cell references) and whilst the formula is copied/pasted, it does not dynamically update to reflect the cell it has been pasted in like it does in Excel. So the question is, how can i modify the below code to allow for the formula to dynamically update? The first formula i am trying to copy is =SUMIF(F8:$F$13,"<>#VALUE!")*C8. When i copy and paste this in Excel into the first destination cell, it updates to =SUMIF(F13:$F$13,"<>#VALUE!")*C13. The broader macro operates on a loop so when it copies and pastes it to the next cell (1 row down), it updates to =SUMIF(F$13:$F14,"<>#VALUE!")*C14 (and so on and so forth).

    Dim srcRange As Object
    Dim destRange As Object
    
    ' Set the source range
    srcRange = OutputSheet.getCellRangeByPosition(8, 7, 10, 7)
    
    ' Set the destination range
    destRange = OutputSheet.getCellRangeByPosition(8, Row, 10, Row)
    
    ' Copy the formulas from the source range to the destination range
    destRange.FormulaArray = srcRange.FormulaArray

Many thanks in advance.

Paul

Please provide enough code so others can better understand or reproduce the problem. — Community
– Community Bot, Commented Jan 5, 2024 at 18:52

JohnSUN · Accepted Answer · 2024-01-06 08:43:58Z

0

Use the fillAuto() method of the Range object

    destRange = OutputSheet.getCellRangeByPosition(8, 7, 10, nLastRow)
    destRange.fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM, 1)

In other words, take the entire range that you want to fill with the formulas, along with the first sample row of the fill, and call the method with the necessary parameters.

answered Jan 6, 2024 at 8:43

JohnSUN

2,5692 gold badges7 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Paul Over a year ago

That is certainly another way of doing it. Thank you JohnSun. I wondered whether I might ask one further question? The final part of the script I am stuck on is the refresh of a pivot table. The photo table is in the same OutputSheet as above and it is the only pivot table in the whole workbook. I just need to refresh/update it once the new data has been added to the output log. If I can do that, I've cracked it!

JohnSUN Over a year ago

@Paul Great! Consider that you have jumped over this obstacle and moved on.

Paul Over a year ago

this makes me very happy! Thank you so much. Script complete!

Collectives™ on Stack Overflow

Paste formula in Libre Calc using Basic macro

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related