Random generator question

[Khanjohn]

I am using =INT(RAND()*100)100-1)+1) and have to use F9 function key fo r
this. Here is problem, anything i put in any other cell changes the number
IE i change E24 from 20 to 21 and the random number in F16 changes. The
problem is anoying as it constantly changes. I had hoped to link it to a
command button so you had to click it but have found no solutions. Any ideea
how to stop the random generator from randomizing except when F9 is pushed?
I am VBA and commandbutton stupid that has been proven to me greatly the past
couple days.

 

[PCLIVE]

Do you have Calculations set to Manual?
Tools-Options-Calculation-Manual

 

[Khanjohn]

no its on auto as if on manual all the other drop down vlookups wont work(

 

[PCLIVE]

I don't think you'll be able to achieve what you want unless you're on
Manual calculation. But as you said, that causes a different issue of
constantly recalculating your random numbers. As you can't keep it from
recalculating, you can write some code to only capture the random numbers
when you click your command button.
For example:

Move your random naumber range to a different location far off to the right
of your worksheet such as columns BA and BB.
Then in your code, copy the current numbers and PasteSpecial to a differnt
location of your worksheet.

So on your command button, your code might be:


Application.Calculate
Range("BA1:BA100").Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False



HTH,
Paul