This would be how it would look at two different points in time:
C3 is the product of A2 and A3. A2 change because this cell is derive from an online score and this will change the value in C3 as well every time A2 is updated.
Perhaps a formula can move the value in C3 to an other cell (in this case D3,D4… ) every time a new value i generated in C3? If this works once perhaps that formula can simply be copied trough out the entire column? And the new permanent table I want would be created?
Sure there are better (automatic) ways to do that, but…
You can fill D3:D28 with the formula =$C$3. Then when a new value is added, you can convert the new result (D3 and below) to value.
To convert a formula to value, there are a few option. Here some:
F2), calculate de value (F9), accept (Enter, Enter)Ctrl+V), and paste as unformatted text (Ctrl+Alt+Shift+V)Ctrl+V), and paste special with default options (Ctrl+Shift+V), accept (Enter)Data - Calculate - Formula to Value (the formula will be seen in the input line while this cell remains active)Also you can skip C3, and fill D3:D28 with the formula =$A$2*$A$3.
Crafty but still manual
Please leave this venture - the idea from the very beginning was not correct.
Let’s look at a specific example. You say
Perfectly. Let the macro remember each new calculated product in the next cell in column D. Once again - “each new”. Now imagine that you get two or more SAME NUMBERS “from an online score”. Well, for example, the number 10 five times in a row. Would you expect five cells to be filled with the value 4440? I assure you, Calc does not expect this. And if you try to teach him “yes, the same numbers should be entered”, then be prepared for the fact that Calc will fill the entire D column with the same value in a matter of seconds.
To perform tasks similar to the one you described, LibreOffice has a special Base component. Yes, it will take time to master. It will even be a little more difficult than writing macros. But - just believe! - Calc is made for other tasks: the input form to fill in the data table isn’t Calc’s task.
Im optimistic and what eeigor put together in his formula isent much less complex. And I would have grate use for sutch a formula in calc . Im sure you have a point tough and offcource there are more complex solutions that also require more skilles.
I have noticed that the data input can be delayed and not delivers in chunks witch I presume is huge advantage here.
Apparently my English is really bad if I can’t explain a simple think.
I’ll try again. Please describe the user action (you or another person) when you receive two invoices with the same value. What should a person do? Clear the old value and enter it again? With this action, the macro will add a cell with a value of 0 and another cell with the same value as before. Or will you press some button to force the macro to enter the current value? If so, then you need both a function and a button that will run a macro to copy a value from one cell to another, right? But if you have a button, why do you need a function?!
First copy your cell C1. Then, if you go to Edit>Paste Special, you should be given “Paste Only Numbers” among the options offered.
@Jalle, you need a macro that will automatically track the change in cell C3 and copy the new value to column D.
Use “com.sun.star.awt.XCallback”.
The conceptual solution is given here. But the code needs to be simplified. You only have one traceable cell.
Edit:
File attached.
save-value (2).ods (15.6 KB)
You can rearrange the cells. The target range must be bounded on the left and right by empty columns and there must be nothing below it.
cool that its possible.
so the formula would be like this: =SAVEDATA(C3;“D5:D28”;D2)
But the macro? Its not easy.
I’ll answer later, if others don’t help.
Try example file above (see solution).
Nothing happens in the target cell. I try to open macro but found nothing
I’ll watch it tonight.
Thanks. Im looking forward to it!
save-value (2).ods (14.2 KB)
Updated
Function SaveValue(Watch, Target$)
''' Args:
''' Watch: Source cell to copy&save its value to new cell of target range.
''' Target: Left-top cell of target range.
Dim ac As Object, callback As Object
ac = CreateUnoService("com.sun.star.awt.AsyncCallback")
callback = CreateUnoListener( "callback_", "com.sun.star.awt.XCallback")
ac.addCallback(callback, Array(Watch, Target))
End Function
Sub callback_notify(aData)
Dim sheet As Object, cursor As Object
Dim lefttopcell As Object, newcell As Object
Dim nCol&, nEndRow&
Dim value: value = aData(0)
On Local Error Resume Next
sheet = ThisComponent.CurrentController.ActiveSheet '.Sheets.getByName("Sheet1")
lefttopcell = sheet.getCellRangeByName(aData(1))
nCol = lefttopcell.CellAddress.Column
cursor = sheet.createCursorByRange(lefttopcell)
cursor.collapseToCurrentRegion()
nEndRow = cursor.RangeAddress.EndRow
newcell = sheet.getCellByPosition(nCol, nEndRow + 1)
newcell.Value = value
End Sub
Method2 (Inspired by @Lupp). This method is preferable. Sheet2 contains a formula that calls a macro whenever the target range is updated.
Try this.
save-value (3).ods (14.3 KB)
No AsyncCallback. No listener used. A simple macro.
Function DoSaveValue(Watch, Target$)
Dim result As Boolean
result = SetValue(Array(Watch, Target))
DoSaveValue = IIf(result, ":done:", ":fail:")
End Function
Function SetValue(aData) As Boolean
On Local Error GoTo Fail
Dim sheet As Object, cursor As Object
Dim lefttopcell As Object, newcell As Object
Dim nCol&, nEndRow&
Dim value: value = aData(0)
sheet = ThisComponent.CurrentController.ActiveSheet '.Sheets.getByName("Sheet2")
lefttopcell = sheet.getCellRangeByName(aData(1))
nCol = lefttopcell.CellAddress.Column
cursor = sheet.createCursorByRange(lefttopcell)
cursor.collapseToCurrentRegion()
nEndRow = cursor.RangeAddress.EndRow
newcell = sheet.getCellByPosition(nCol, nEndRow + 1)
newcell.Value = value
SetValue = True
Fail:
End Function
Excel has an Application.Volatile method. In Calc, the macro is volatile by default.
NOTE: The algorithm uses the collapseToCurrentRegion() cursor method, so the target range must be surrounded by empty columns on the left and right.
This is where it all comes from.
Edit:
Nuance. When you open the file, the ThisComponent.CurrentController.ActiveSheet method is not yet available, so an error occurs. But this is the desired behavior, because in this case the last value is not duplicated. And in cell A1 you see the result :fail:
When using the first method, this harmful effect did not occur, but only connected the listener.
Which method is better?
Amazing! But how do I use it? When changing number in A and B nothing happens. I try to look for macro under toles find nothing. Sould I past the macro somewhere?
I’m probably missing something very fundamental here!
The file contains already a ‘module1’ with all macros. And they are posted. I don’t understand what’s the matter.
Edit: Perhaps the low macro security level is set…
Edit: Of course I was wrong. In this mode, all macros are executed at all times.
After a a break from this project. I see now that it actually works! Thanks a lot. And hail the smart!
In principal this is exactly what I like to do. But not exactly.
In the sheet (macro) that would salve my problem and achieve a partial goal of mine (And enable me to move on and learn some programming).
There would be 5 cells that would correspond to the cells now called Val1 and 2"
Each of those individual cells would feed 5 other cells. I guise the cells that are “feed” could be compared to the cell now called “product”. But importantly there is no calculation (that’s done separately and works fine) her it just copying. And of course the five columns with the cell that would correspond the the cells now called product would fill up as it dose now!
And of course that’s what is the feat that this macro can do! And the alteration Im looking for is probably not more difficult than what the sheet can do now. As I suppose the fundamental problem is salved.
I looked at the macro and is completely incomprehensible to me.
line 4 to 12 is some what reasonable (although the cells the function refers to is empty) but after they I dont understand a thing.
Dose any one know how to sett up macro that do the specif thing I describe above? Importantly the 5 cells/columns that are being feed have to movable or at the very least be situated some where around line 4 and specifically in column B,C,D,E,I,J (leaving the A column blank). The cells feeding this 5 cells could preferably be a few line a abow possible in row 2.
There is an other issue and that is that the current macro cant deal with decimals.
