Freezing the cell (with Random formula)

Everytime I copy the cell (wth rand() formula) and paste it to another cell
(use paste special - value - ok), the value of the original cell changes. How
can I freeze the value of the original cell? Thanks a lot !!

SEE! I'm not the only one!



I have been trying to figure that one out for weeks.



"Christy" <[email protected]> wrote in message
news:[email protected]...
> Everytime I copy the cell (wth rand() formula) and paste it to another
> cell
> (use paste special - value - ok), the value of the original cell changes.
> How
> can I freeze the value of the original cell? Thanks a lot !!

Well, that's what the RAND() function does...right?
So, here's my thought:
Why not put the RAND() function someplace where it will do no harm, then
copy/paste_values the latest random value where you need it?

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Christy" wrote:

> Everytime I copy the cell (wth rand() formula) and paste it to another cell
> (use paste special - value - ok), the value of the original cell changes. How
> can I freeze the value of the original cell? Thanks a lot !!

Forgive the earlier smart-alec post, as I said I have been searching fro the
answer to this one for awhile. I have found 1 way to "freeze" the rand()
thus far.

If you have a checkbox linked to cell A1
cell B1 =IF(A1=TRUE,rand(<whatever>),"")

because a checkbox linked cell does not recalculate unless the checkbox is
triggered, the rand() will not recalculate. It only solves about half of my
issues wich is why I am still trying to find a way to freeze the cell by its
own formula, but maybe this will work for you.

"Christy" <[email protected]> wrote in message
news:[email protected]...
> Everytime I copy the cell (wth rand() formula) and paste it to another
> cell
> (use paste special - value - ok), the value of the original cell changes.
> How
> can I freeze the value of the original cell? Thanks a lot !!

Is there a way (by formula) to copy by value only?

On further thought, the cell would still change whenever excel recalculates.
A1 = Y
B1 = Rand()
C1 = =IF(A1="Y",<copy.by.value>B1,"")

Every time excel recalculates, B1 will bet a new number, and C1 will realize
A1 is "Y" then retreive another number...

still same problem.

"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> Well, that's what the RAND() function does...right?
> So, here's my thought:
> Why not put the RAND() function someplace where it will do no harm, then
> copy/paste_values the latest random value where you need it?
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Christy" wrote:
>
>> Everytime I copy the cell (wth rand() formula) and paste it to another
>> cell
>> (use paste special - value - ok), the value of the original cell changes.
>> How
>> can I freeze the value of the original cell? Thanks a lot !!

Not really....functions return values, but cannot change the actual contents
of a cell. So, a formula won't be changed into a constant by a function.

***********
Regards,
Ron

XL2002, WinXP-Pro


"Adam Kroger @hotmail.com>" wrote:

> Is there a way (by formula) to copy by value only?
>
> On further thought, the cell would still change whenever excel recalculates.
> A1 = Y
> B1 = Rand()
> C1 = =IF(A1="Y",<copy.by.value>B1,"")
>
> Every time excel recalculates, B1 will bet a new number, and C1 will realize
> A1 is "Y" then retreive another number...
>
> still same problem.
>
> "Ron Coderre" <[email protected]> wrote in message
> news:[email protected]...
> > Well, that's what the RAND() function does...right?
> > So, here's my thought:
> > Why not put the RAND() function someplace where it will do no harm, then
> > copy/paste_values the latest random value where you need it?
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "Christy" wrote:
> >
> >> Everytime I copy the cell (wth rand() formula) and paste it to another
> >> cell
> >> (use paste special - value - ok), the value of the original cell changes.
> >> How
> >> can I freeze the value of the original cell? Thanks a lot !!
>
>
>

Just turn off auto calculation!
<Tools> <Options> <Calculation> tab,
And check "Manual".

After you copy the value, change it back.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Christy" <[email protected]> wrote in message
news:[email protected]...
> Everytime I copy the cell (wth rand() formula) and paste it to another
cell
> (use paste special - value - ok), the value of the original cell changes.
How
> can I freeze the value of the original cell? Thanks a lot !!

When you turn the auto calc on, won't the sheet recalc, and then the
original number will change. The original cells randomly determined value,
needs to remain constant (stable) once assigned. Even if the sheet doesn't
recalc immediatly, it will recalc the next time data is entered into any
cell. What is needed is a UDF that will look at the cell's contents and

if(ISNUMBER(cells.existing.content),cells.existing.content,rand())

of course cells.existing.content is imaginary. The real trick, is managing
to do this without causing a circular reference. maybe a combination of UDF
calling a macro, as they seem to live outside of excel's notice for circular
checks.

UDF reports the cell location to a macro, that then returns the current
cell.value to the UDF wich makes the isnumber evaluation and returns either
a rand() or the value given by the macro. Unfortunatly, I could be wrong,
but I think recalc performance would probably be effected by the macrocall,
especially if the UDF is used in many locations.


"RagDyer" <[email protected]> wrote in message
news:[email protected]...
> Just turn off auto calculation!
> <Tools> <Options> <Calculation> tab,
> And check "Manual".
>
> After you copy the value, change it back.
> --
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "Christy" <[email protected]> wrote in message
> news:[email protected]...
>> Everytime I copy the cell (wth rand() formula) and paste it to another
> cell
>> (use paste special - value - ok), the value of the original cell changes.
> How
>> can I freeze the value of the original cell? Thanks a lot !!
>

Hi, here's one way.

Start by inserting a new worksheet, in any cell put =RAND() and copy down as far as needed, next copy those cells and paste them over your current cell containing your rand() formula....Paste as link. Drag the worksheet to the far left making worksheet 1, you can now hide the worksheet. In the worksheet where you just pasted those cell, insert 2 radio button from the visual basic toolbar, name 1 "Calc On" the other "Calc Off" then put this in the Calc On code:

Private Sub OptionButton1_Click()
Worksheets(1).EnableCalculation = True
End Sub
....this will turn on calculation of the new worksheet generating new random numbers

and this into Calc Off

Private Sub OptionButton2_Click()
Worksheets(1).EnableCalculation = False
End Sub
...this will turn off calculation of the new worksheet freezing the random numbers until calculation is turn on again

then Exit out of design mode

now when you need to generate new random numbers select Calc On then Calc Off, as long as Calc Off is selected those random numbers will not change.

It may be out of the ordinary but it should work.

HTH
JG

In article <[email protected]>, "Adam Kroger" <adam_kroger<nospam>@hotmail.com> wrote:
>Forgive the earlier smart-alec post, as I said I have been searching fro the
>answer to this one for awhile. I have found 1 way to "freeze" the rand()
>thus far.
>
>If you have a checkbox linked to cell A1
>cell B1 =IF(A1=TRUE,rand(<whatever>),"")
>
>because a checkbox linked cell does not recalculate unless the checkbox is
>triggered, the rand() will not recalculate. It only solves about half of my
>issues wich is why I am still trying to find a way to freeze the cell by its
>own formula, but maybe this will work for you.

Interestingly, this is something that OO handles quite differently.I've
tried a similar sounding spreadsheet in both. If an input cell changes, OO
recalculates, otherwise it doesn't. XL seems to recalculate every time you
move the cursor or some such.

>"Christy" <[email protected]> wrote in message
>news:[email protected]...
>> Everytime I copy the cell (wth rand() formula) and paste it to another
>> cell
>> (use paste special - value - ok), the value of the original cell changes.
>> How
>> can I freeze the value of the original cell? Thanks a lot !!
>
>

Bruce

----------------------------------------
I believe you find life such a problem because you think there are the good
people and the bad people. You're wrong, of course. There are, always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)

"Christy" wrote:
> Everytime I copy the cell (wth rand() formula)
> and paste it to another cell (use paste special -
> value - ok), the value of the original cell changes.
> How can I freeze the value of the original cell?

This is a very reasonable request. There is nothing
antithetical with the concept of RAND().

You might try searching for the discussion entitled "VBA
code to populate 1000's of cells" in the excel.misc or
"General Questions" newsgroup. The example is indeed
about using RAND() in a manner that does not recalculate
automatically without having to disable auto recalculate
across-the-board, which is generally undesirable to do.

Three responders -- JE McGimpsey, Bernard Liengme and
David McRitchie -- each provide important pieces of the
solution. You might want to take a look at all three
postings, since each one selects the cells slightly differently.
By the way, Bernard posting has excellent step-by-step
procedures for entering a macro, if you are unfamiliar
with doing that.

The following is my summary. Click Tools > Macros >
Visual Basic Editor or type alt-F11. In the VBE, click
Insert > Module and enter the following macro:

Sub MyRand()
For Each cell In Selection
cell.Formula = "=RAND()"
cell.Value = cell.Value
Next cell
End Sub

(Note: I am a VBA novice. There might be more
elegant solutions.)

Close the window. You can rename the module name
by clicking "Module1" in the lower left window and
typing "MyRand". Close the VBE window.

In the spreadsheet, select the desired cells, type alt-F8,
highlight the MyRand macro (if necessary) and click Run.
Voila! The cells contain random values, not the formula
"=RAND()". Obviously, those values will never change
unless you select those cells and execute the macro again.

JE and Bernard show ways to code the macro so that
the random values always go into a fixed range, if that
is what you want.

Turn off auto calc,
Type =RAND(),
Hit <Enter>,
Right click in RAND() cell,
Choose "Copy",
Right click again,
Choose "Paste Special",
Click on "Values",
Then <OK>.
Return calc to auto.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
news:[email protected]...
When you turn the auto calc on, won't the sheet recalc, and then the
original number will change. The original cells randomly determined value,
needs to remain constant (stable) once assigned. Even if the sheet doesn't
recalc immediatly, it will recalc the next time data is entered into any
cell. What is needed is a UDF that will look at the cell's contents and

if(ISNUMBER(cells.existing.content),cells.existing.content,rand())

of course cells.existing.content is imaginary. The real trick, is managing
to do this without causing a circular reference. maybe a combination of UDF
calling a macro, as they seem to live outside of excel's notice for circular
checks.

UDF reports the cell location to a macro, that then returns the current
cell.value to the UDF wich makes the isnumber evaluation and returns either
a rand() or the value given by the macro. Unfortunatly, I could be wrong,
but I think recalc performance would probably be effected by the macrocall,
especially if the UDF is used in many locations.


"RagDyer" <[email protected]> wrote in message
news:[email protected]...
> Just turn off auto calculation!
> <Tools> <Options> <Calculation> tab,
> And check "Manual".
>
> After you copy the value, change it back.
> --
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "Christy" <[email protected]> wrote in message
> news:[email protected]...
>> Everytime I copy the cell (wth rand() formula) and paste it to another
> cell
>> (use paste special - value - ok), the value of the original cell changes.
> How
>> can I freeze the value of the original cell? Thanks a lot !!
>

Just a note about this portion:

Close the window. You can rename the module name
by clicking "Module1" in the lower left window and
typing "MyRand". Close the VBE window.

Don't do it.

Excel can get confused when the module name is a duplicate of a function/sub
name. Better to either leave it alone (Module#) or just rename it to something
mnemonically significant: Mod_MyRand or Func_MyRand.



[email protected] wrote:
>
> "Christy" wrote:
> > Everytime I copy the cell (wth rand() formula)
> > and paste it to another cell (use paste special -
> > value - ok), the value of the original cell changes.
> > How can I freeze the value of the original cell?
>
> This is a very reasonable request. There is nothing
> antithetical with the concept of RAND().
>
> You might try searching for the discussion entitled "VBA
> code to populate 1000's of cells" in the excel.misc or
> "General Questions" newsgroup. The example is indeed
> about using RAND() in a manner that does not recalculate
> automatically without having to disable auto recalculate
> across-the-board, which is generally undesirable to do.
>
> Three responders -- JE McGimpsey, Bernard Liengme and
> David McRitchie -- each provide important pieces of the
> solution. You might want to take a look at all three
> postings, since each one selects the cells slightly differently.
> By the way, Bernard posting has excellent step-by-step
> procedures for entering a macro, if you are unfamiliar
> with doing that.
>
> The following is my summary. Click Tools > Macros >
> Visual Basic Editor or type alt-F11. In the VBE, click
> Insert > Module and enter the following macro:
>
> Sub MyRand()
> For Each cell In Selection
> cell.Formula = "=RAND()"
> cell.Value = cell.Value
> Next cell
> End Sub
>
> (Note: I am a VBA novice. There might be more
> elegant solutions.)
>
> Close the window. You can rename the module name
> by clicking "Module1" in the lower left window and
> typing "MyRand". Close the VBE window.
>
> In the spreadsheet, select the desired cells, type alt-F8,
> highlight the MyRand macro (if necessary) and click Run.
> Voila! The cells contain random values, not the formula
> "=RAND()". Obviously, those values will never change
> unless you select those cells and execute the macro again.
>
> JE and Bernard show ways to code the macro so that
> the random values always go into a fixed range, if that
> is what you want.

--

Dave Peterson