Ask Your Question
1

Returning random number and save for later use [closed]

asked 2014-10-05 19:57:28 +0100

RJberg gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-04 19:11:32.809576

2 Answers

Sort by » oldest newest most voted
0

answered 2014-10-06 10:50:15 +0100

RJberg gravatar image

updated 2014-10-06 10:54:46 +0100

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.

edit flag offensive delete link more

Comments

Now you should be allowd to upload a file.

Lupp gravatar imageLupp ( 2014-10-06 17:58:18 +0100 )edit

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 gravatar imageRJberg ( 2014-10-07 14:52:42 +0100 )edit
0

answered 2014-10-05 23:43:03 +0100

Lupp gravatar image

updated 2014-10-08 00:46:28 +0100

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

edit flag offensive delete link more

Comments

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

RJberg gravatar imageRJberg ( 2014-10-06 14:25:29 +0100 )edit

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 gravatar imageRJberg ( 2014-10-07 16:58:59 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2014-10-05 19:57:28 +0100

Seen: 739 times

Last updated: Oct 08 '14