Thread: Locking RAND()?
View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Bernie Deitrick Bernie Deitrick is offline
external usenet poster
 
Posts: 5,441
Default 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