Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking RAND()?
Hi everyone,
Say you have in A1, A2 two formulas with Rand() in each. Say you have a macro that does freez A1 when it hits its target value, while A2 keeps running till it hits its target value. My question is how to freez Rand()? Thanks, Jo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking RAND()?
You would have to replace the =RAND() formula with its value when you want
to "freeze" it. E.g., Range("A1").Value = Range("A1").Value Setting the Value property wipes out the formula so the cell will no longer be updated (ever). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Jo" wrote in message ups.com... Hi everyone, Say you have in A1, A2 two formulas with Rand() in each. Say you have a macro that does freez A1 when it hits its target value, while A2 keeps running till it hits its target value. My question is how to freez Rand()? Thanks, Jo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking RAND()?
Jo,
Perhaps something along these lines - put the formula =RAND()*ROW()*13 into both A1 and A2, and run the macro.... Sub FreezeRandValues() Dim TargetValue1 As Double Dim TargetValue2 As Double 'Formulas in A1 and A2 are ' =RAND()*ROW()*13 TargetValue1 = 12.89 TargetValue2 = 25.89 While Range("A1").Value < TargetValue1 Application.Calculate If Range("A2").Value TargetValue2 Then Range("A2").Value = Range("A2").Value End If Wend Range("A1").Value = Range("A1").Value While Range("A2").Value < TargetValue2 Application.Calculate Wend Range("A2").Value = Range("A2").Value End Sub -- HTH, Bernie MS Excel MVP "Jo" wrote in message ups.com... Hi everyone, Say you have in A1, A2 two formulas with Rand() in each. Say you have a macro that does freez A1 when it hits its target value, while A2 keeps running till it hits its target value. My question is how to freez Rand()? Thanks, Jo |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking RAND()?
The Rand function is XL is volatile which means that it recals every time a
calcualtion occures. Here is a UDF that is not volatile so it will evealuate when it is first entered but not with each calculation. Public Function StaticRand(Optional ByVal rng As Range) As Single StaticRand = Rnd() End Function To force it to recalc you can point it at a cell and then just change the value of that cell. Add the code to a standard code module. Use it in a sheet something like this... =staticrand() The above is truely static and will only change if you edit the cell the formula is in. =staticrand(A1) The above is static until you change the value of A1 at which point the formula will re-evaluate. -- HTH... Jim Thomlinson "Jo" wrote: Hi everyone, Say you have in A1, A2 two formulas with Rand() in each. Say you have a macro that does freez A1 when it hits its target value, while A2 keeps running till it hits its target value. My question is how to freez Rand()? Thanks, Jo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i set up rand or rand between to give only -1 or 1? | Excel Worksheet Functions | |||
=INT(0+(1-0+1)*RAND()) wtf? | Excel Worksheet Functions | |||
RAND() | Excel Discussion (Misc queries) | |||
locking formula in cells in without locking whole sheet | Excel Discussion (Misc queries) | |||
RAND() | Excel Discussion (Misc queries) |