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

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

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

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