Help Please

[Joe]

I have column A generating random numbers. In column B I have values
of either true or false assigned to the random numbers in column A.
What I want to do in Column C (hopefully) is to say if column B is
true, then go back and come up with a new random number in column A
until column B becomes false.

I'll try to illustrate.


A1=Rand() B1=Flase C1=Column B is Fine
A2=Rand() B2=Flase C2=Column B is Fine
A3=Rand() B3=True C3=Since B3=true,
generate a new # in A3
until B3 becomes false
A4 =Rand() B4=False C4=Column B is Fine


I need to know how/if column C can tell column A to come up with a new
#.

 

[Mike]

Why not just create the random number in a way that
guarantees that the condition is true in the first place?
What is the condition?

 

[Harald Staff]

Hey Joe

A function can do one thing only: return a value to its own cell.

So: Something whatever in C can NOT change anything in A. Unless A has a
function that depends on C (if so the A function is changing A, not C).

This is either impossible, or it requiring serious a circular reference
setup (quite complicated), or it equires a macro solution.

HTH. Best wishes Harald

 

[jeff]

Hi, Joe,

Here's a little macro that may help: Remember if
all your cells in Col A use the RAND() function, then
hitting recalc (F9) will recalculate all of them.
What this macro does is finds all the "False" cells
in col b and copies and pastes special the A cell
so the value is preserved (it is no longer a RAND()
functioning cell). YOu have to select all the cells
each time, hit F9, run the macro and repeat until
all your values are False.

May not be what you want, but seems like fun!!
enjoy,
jeff



Sub setFalse()
Dim r As Range
Set r = Selection
For Each c In r
If c.Offset(0, 1) = "False" Then
c.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next c
End Sub