Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 30 Dec 2018 03:04:36 +0100Creating a Macro that will run functions in certain columns.https://ask.libreoffice.org/en/question/177715/creating-a-macro-that-will-run-functions-in-certain-columns/ Hi Everyone!
I have 3 tabs "Instructions", "Raw_Data", and "Clean_Data". In the Clean_Data tab I have the following formulas written directly on the calc spreadsheet.
In Column A: =IF(ISNUMBER($Raw_Data.A1)=1,$Raw_Data.A1,"WRONG!")
In Column B: =IF(ISNUMBER($Raw_Data.B1)=1,$Raw_Data.B1,"WRONG!")
In Column C: =IF(ISNUMBER($Raw_Data.C1)=1,$Raw_Data.C1,"WRONG!")
In Column E: =$Raw_Data.B1
In Column F: =IF(ISNUMBER($Raw_Data.A1)=1,"I'm a number",IF(LEFT($Raw_Data.A1)="*",MID($Raw_Data.A1,2,4),MID($Raw_Data.A1,1,4)))
In Column G: =IF(F453="I'm a number"," ",IF(MID($Raw_Data.A1,6,4)="DUPL"," ",IF(MID($Raw_Data.A1,7,4)="DUPL"," ",IF(MID($Raw_Data.A1,6,1)="/",MID($Raw_Data.A1,7,4),MID($Raw_Data.A1,6,4)))))
In Column H: =IF(MID($Raw_Data.A1,6,4)="DUPL","DUPL",IF(MID($Raw_Data.A1,7,4)="DUPL","DUPL",IF(MID($Raw_Data.A1,11,4)="DUPL","DUPL",IF(MID($Raw_Data.A1,12,4)="DUPL","DUPL"," "))))
In Column I: =IF(LEFT($Raw_Data.A1)="*","*"," ")
Is there anyway I can enter these formulas into a macro and have them run through all the rows in the raw data sheet that contain text and the print the formula values on the clean data sheet? I'm trying to automate as much as possible and limit the chances for someone to be able to change the formulas by mistake.
Thanks for you help!mxc5425Sun, 30 Dec 2018 03:04:36 +0100https://ask.libreoffice.org/en/question/177715/How to reference an old document in a formulahttps://ask.libreoffice.org/en/question/147929/how-to-reference-an-old-document-in-a-formula/ I have a running tabulation of the gallons of 9 different products i have on my truck. I save the files as "03-01-2018", "03-02-2018" etc etc etc.
What I would like to know is how (either by formula or macro) to reference a cell in yesterdays file. I already have a cell in the document that has the date in it. When I open "#Daily Tabulations.ods", I want it to look at yesterdays file (Using A2 which has the date on the miscellaneous sheet -1 preferably), as well as on the miscellaneous sheet, at cells B7:B14, and use those to fill in the cells A2, C2, and E2 on gasoline and diesel sheets, and just A2 and C2 on the Kerosene sheet. But only if they're empty. I don't want it to replace data. I will be using this all year so I need it to only look at the previous day. I also need it to have if statements (maybe?) So if it's 3/5/18 and the last one I have is 3/1/18 because I had a 3 day weekend, it will use that file. Sorry for asking for so much and sorry if I've confused you.
I have attached 2 files. The first one is today's running totals. The second is my template that I open each day.
[03-01-2018.ods](/upfiles/15199312312971045.ods)
[#Daily Tabulations.ods](/upfiles/1519931160752968.ods)crosenbauer2112Thu, 01 Mar 2018 20:10:42 +0100https://ask.libreoffice.org/en/question/147929/Macro: how to get a cell value set by a formulahttps://ask.libreoffice.org/en/question/83622/macro-how-to-get-a-cell-value-set-by-a-formula/ Hi
I've written the following function, it just reads in a cell value and puts a wrapper around the detailed steps. It seems to work ok except when the cell of interest has been set by a formula. What I want is for the result of the Macro function to be set to the result of the spreadsheet formula, instead the macro function is set to the spreadsheet formula code.
QUESTION - what change should I make to the function?
Function get_cell( sheetname_str, col,row)
'sheetname_str e.g "Sheet1", 'col and row are integers
'Return value can be VALUE or TEXT
Dim localdoc as object
Dim localsheets as object
Dim my_sheet as object
Dim my_cell as object
Dim cell_value
localdoc = ThisComponent
localsheets = localdoc.Sheets
my_sheet = localsheets.getByName(sheetname_str)
my_cell = my_sheet.getCellByPosition(col,row)
Select Case my_cell.Type
Case com.sun.star.table.CellContentType.VALUE
cell_value = my_cell.Value
Case com.sun.star.table.CellContentType.TEXT
cell_value = my_cell.String
Case com.sun.star.table.CellContentType.FORMULA
cell_value = my_cell.FormulaLocal
End Select
get_cell = cell_value
End FunctionmildlyoddSat, 10 Dec 2016 23:00:29 +0100https://ask.libreoffice.org/en/question/83622/Setting a formula in macro basichttps://ask.libreoffice.org/en/question/77399/setting-a-formula-in-macro-basic/Morning,
Trying to set a formula in a specific cell, but it keeps recognising it as just text...
Using the following code:
sheet.getCellByPosition(4,4).String = date()
sheet.getCellByPosition(4,5).String = InvoiceNum
sheet.getCellByPosition(3,16).String = "=IF(B17="";"";1)"
but it keeps coming up a text, and (obviously) not working.
Others work fine, and looking at the Watch results, looks like it should be ok (ie Formula = "=IF(H5="";"";15)".
Anyone?
Thanks.hornetster@gmail.comFri, 16 Sep 2016 03:21:39 +0200https://ask.libreoffice.org/en/question/77399/Changing variable in a formula (Updating input of function)https://ask.libreoffice.org/en/question/75239/changing-variable-in-a-formula-updating-input-of-function/ Hi,
I have started to write a macro to create document easily.
It is going to finish but I found a mistake in my macro. When I update input of my function, it updates variable of my formula. But it gives error.
*My Formula:*
`oCellYD.setFormula("=K" & iRowYD + 1 "*L" & iRowYD + 1 "*N" & iRowYD + 1 & "*M" & iRowYD + 1 & "/O" & iRowYD + 1)`
It gives error. That is:
**"BASIC syntax error.
Parentheses do not match."**
I guess, it is about iRowYD variable. Every time, I call my function, iRowYD variable changes(increases).
Where should I change to fix this issue.
Sorry for my bad English.
Thanks for your time. mustafaxfeTue, 16 Aug 2016 13:41:49 +0200https://ask.libreoffice.org/en/question/75239/Error: Err:508 problem while writing macro(setFormula) with libreoffice basichttps://ask.libreoffice.org/en/question/74920/error-err508-problem-while-writing-macrosetformula-with-libreoffice-basic/Hi,
I have started to write a macro to organize my file as I want. I am going to finish it but I have locked a problem.
When I try to write a formula with setFormula, it gives that error **Err:508**
My Formula: `oCellYD.setFormula("=IF((M" & i & "<>"""")," & "O" & i & ",0)")`
It looks `oCellYD.setFormula("=IF((M" & i & "<>")," & "O" & i & ",0)")` as in libreoffice input line. So adding a `"` in the middle '`<> "`' => '`<> ""`' of formula by hand at input line it fixes.
And also when I Change my formula as ` oCellYD.setFormula("=IF((M" & i & "<>"")," & "O" & i & ",0)")`
It gives **#NAME?** error. And If add a quotation mark as in **Err:508**, It fixes.
So my question, how can I fix this issue in macro.mustafaxfeWed, 10 Aug 2016 07:40:36 +0200https://ask.libreoffice.org/en/question/74920/Using Calc Formula Function with Macrohttps://ask.libreoffice.org/en/question/46324/using-calc-formula-function-with-macro/Dear LibreOffice Community,
I wrote a calc macro like: Cell.Formula = "=MIN(B4, B5)"
Im my cell, the formula is correct : "=MIN(B4, B5)". But the value is Err:508
If I edit the formula, remove a character and put it back. The value is computed correctly.
There is the Bug 59916. It looks like to be my problem.
What should I do to make it working?
Thank you.
LibreOffice Version: 4.3.3.2 Build ID: 430m0(Build:2)
OS: Ubuntu 14.10LaurentOliveSat, 14 Feb 2015 15:29:42 +0100https://ask.libreoffice.org/en/question/46324/Calc: How do I sort one range of cells according to the values in a second range of cells, and place the results in a third range of cells?https://ask.libreoffice.org/en/question/40398/calc-how-do-i-sort-one-range-of-cells-according-to-the-values-in-a-second-range-of-cells-and-place-the-results-in-a-third-range-of-cells/Windows 7 SP1. LibreOffice 4.2.6.3. Calc.
I would like to sort a list of values in one range of cells into an order determined by a co-related list of values in a second range of cells, and place the resulting sorted list into a third range of cells (I would like to leave the original list of values, unsorted, where they are).
A simplified example:
The range B11:B20 contains a list of "Labels"/"Names" (of items): "A","B","C","D","E","F","G","H","I","J".
The range C11:C20 contains a co-related list of attribute values: 5, 17, 49, -3, 15,145, 49,8.5, 17, 9.
(Note: The attribute values may not all be unique).
The resulting sorted list (of items) placed in C31:C40 would be: "D","A","H","J","E","B","I","C","G","F".
I cannot see an easy/straightforward way to do this.
I can only imagine something like having to write formula statements with a lot of (nested) "IF" Functions. (And, as the actual lists contain 50 items, and there are multiple co-related lists of attributes, that will be a LOT of "IF"s!) A somewhat daunting prospect! Please help! (Do I need to learn to write Macros? I'm not averse to that, where do I start?)
----------
UPDATE: In lieu of an attachment here's the Less Simplified Example in text form:
<PRE>
Daily Cumulative Items arranged
A B C D E F A B C D E F by cumulative score
2014/09/25 1 1 0 1 0 0 0 1 B F A C D E
2014/09/26 1 2 1 1 0 0 2 1 E A B F C D
2014/09/27 3 1 1 0 3 2 1 D E A B F C
2014/09/28 4 1 1 4 3 2 1 C D E A B F
2014/09/29 2 6 1 3 4 9 2 1 D C B E A F
2014/09/30 6 3 7 3 4 9 5 1 D A E C B F
</PRE>
I'm looking for a formula for the "Items arranged by cumulative score" rows so that when I add new data I can just copy the last row, put the new details in the "Daily" columns and the formulas will work out the "Cumulative" results (done that bit) and then arrange the Items in order.
Thanks in advance for any suggestions!
----------
UPDATE: Added the Less Simplified Example as an attachment [Less_Simplified_Example.ods](/upfiles/14121620899549537.ods)
I'm looking for a formula to work out the results in the shaded area.
The thought occurs that formulae seem to give the result for a single cell, whereas I want a result for a range of cells. Does this mean that I'm going to have to write a macro?
H.GSun, 28 Sep 2014 19:43:28 +0200https://ask.libreoffice.org/en/question/40398/How to write my own function to work in matrix formulas?https://ask.libreoffice.org/en/question/32776/how-to-write-my-own-function-to-work-in-matrix-formulas/I work in LibreOffice 4.0.6.2. I have three columns of numeric data (double) as my input. I want to get one numeric column (double) as my function output. I wanted to write my own public function accessible from worksheet which can be used in matrix formulas. Currently I wrote already my 1st version of this function which returns a scalar double and takes 3 scalars as its input, but it needs in each line another different relative addressing references. It works well of course, but I hoped that I would use matrix calculation. Such construction works for standard math operator. I mean for example such syntax {=A1:A3/C1:C3} or even this better looks when you define named range: {=my_a_range/my_c_range}. I hoped that I would use {=my_fun(col_a, col_b, col_c)} in my whole output column where col_a, col_b, col_c are named input column of data.
The main reason of this operation is virtually increasing formulas readability in my worksheet. I can check that everywhere is the same formula instead of having the same function with incrementing address referencing.
I have read LO documentation, additional documentation about macro. I did not read SDK documentation. Where should I go to achieve this goals in terms of additional reading, spotting some examples etc.?MichTue, 15 Apr 2014 20:37:14 +0200https://ask.libreoffice.org/en/question/32776/macro overlays formula in cellhttps://ask.libreoffice.org/en/question/7347/macro-overlays-formula-in-cell/When a cell is selected and it contains a formula, Calc by default shows the formula in the formula bar and the calculation result in the cell.
But when I code <I>=MyMacro()</I> in a cell and MyMacro() saves a result in the cell with <I>thisComponent.currentController.selection.setValue(result)</I> the formula is overwritten with the result and the call to MyMacro() is lost. The result displays in both the selected cell and in the formula bar.
What did I do wrong or fail to do?RonThu, 01 Nov 2012 02:07:34 +0100https://ask.libreoffice.org/en/question/7347/