Returning random number and save for later use

[RJberg]

I found a lot of solutions on random number generation and saving, but none of those fit my need or helps me to solve my problem.

I’m trying to generate a random number in Calc and want to save this number for use later and be able to generate another number to use again. What i get is that it or generates a number, but it is not usable because if i want to generate a new number it changes the number i used too. I have some experience with VBA in excel, but i’m having difficulties to get an understanding on the programming in libre.

So now what i would like to do.
I have a sheet (sheet1) on this sheet i have a command button that i want to use to generate a number. This number has to be added to a table in sheet 2 each time i click the button on shhet1. The number should not be changed on sheet2 when i click the button on sheet 1 i only want to add it for later use. Is this possible to achieve or am i asking too much here?

anyone that can help me out here?

[Lupp]

Command buttons aren’t my preferred toy. But keeping a set of random numbers for a while and recalculating them “on command” I needed sometimes. Just look into the attached example to see if the approach there is something for you.

ask40730RandomRecalcOnCommand001.ods

Editing (in reply to the answer posted by the questioner himself):
I won’t recommend it, but you may have a look into an adapted version of a study I made some time ago. See attached, please. Sorry! Due to my mistake you first got a defective version of the example. the new version should work as intended.

StudyCountChangeWatch002Special.ods

Of course programming a Sub, assign it to a PushButton, … is also possible. The disadvantages of Calc documents containing user defined programing should be taken in account.

If urgently wanted a solution by ‘CommandButton’, see next attachment. It will need a finish!

The Sub

Sub GenerateRandomNumberCountAndPut()
Dim oDoc As Object
Dim oSheet As Object
Dim oCell1, oCell2,oCell3 as Object
oDoc = ThisComponent
oSheet = oDoc.Sheets.GetByName("Sheet1")
oCell1 = oSheet.GetCellByPosition(0,0)
oCell2 = oSheet.GetCellByPosition(1,0)
oCell1.Value = Rnd()
If oCell2.Value <= 0 Then oCell2.Value = 0
oCell2.Value = oCell2.Value+1
oCell3 = oSheet.GetCellByPosition(4,oCell2.Value-1)
oCell3.Value = oCell1.Value
End Sub

is assigned to the event ‘Mouse button released’ of the PushButton.

ask40730RandomRecalcOnCommand001a.ods

[RJberg]

Thank you for this one. I think i will be able to use thisone to fit my needs.

[RJberg]

Tried to get this to work as i want, but unfortunately it recalculates the values every time i type something in my sheet. In Excel i can turn of the function and use F9. But i would like to be able to Lock the recalculation of the random numbers, but still automatically calculate the other cells. Can i Lock the calculation from being executed by something like an IF function (se my 2nd. post)?

[RJberg]

Thank you for Your help, but this is what i had found out myself. The problem is that i can’t “Lock” the rest for recalculating. If i change the value to Yes in Your example it recalculates all the values. I would like to create a kind of “Lock” for recalculating values once they are randomly chosen so they wont change at the next random Choice.

I have a sheet (sheet1) on this sheet i have a cell that i want to use to generate a number. This number has to be added to a table in sheet 2 each time i change the value of a cell on sheet1. The number i generated earlier should not be changed on sheet2 when i retrieve a number on sheet1 i only want to add it for later use.

Something like (A10) means cell A10 in the spreadsheet:
(A10) Generate New number → (B10) Y → (C10) here you show the generated number <–If B10 is “Y” then generate new value for C10 else keep this number. But if B10 is “No” then only change C10 and not C11 etc.
(A11) Generate New number → (B11) Y → (C11) here you show the generated number <–If B11 is “Y” then generate new value for C11 else keep this number. But if B11 is “No” then only change C11 and not C10 etc.

I can’t upload a file unfortunately.

[Lupp]

Now you should be allowd to upload a file.

[RJberg]

Tried to get this to work as i want, but unfortunately it recalculates the values every time i type something in my sheet. In Excel i can turn of the function and use F9. But i would like to be able to Lock the recalculation of the random numbers, but still automatically calculate the other cells. Can i Lock the calculation from being executed by something like an IF function (se my 2nd. post)?