Sample a random calculated cell

[Diogo]

Hi

Need help with the following:

I've a cell that is calculated with the random function

I need a procedure that simulates me hiting the F9 button and copying the
result to a column so I can make a statistic to see how many values are above
a certain value? lets say I need 100 values to have a representative sample.
How can I make this?

Please help. Thanks.

 

[Rick Rothstein \(MVP - VB\)]

Give this subroutine a try...

 

[Rick Rothstein \(MVP - VB\)]

Give this subroutine a try...

Sub GenerateValues(NumberOfTimes As Long, ColumnLocation As Variant, StartRow As Long)
Dim X As Long
Static AlreadyRun As Boolean
If Not AlreadyRun Then
Randomize
AlreadyRun = True
End If
For X = 1 To NumberOfTimes
Cells(StartRow + X - 1, ColumnLocation).Value = Rnd
Next
End Sub

Simply call it from within your own code passing in the three arguments. For example, to create 100 random values greater than or equal to 0 and less than 1 (you didn't specify any range of numbers to draw your values from, so I went with the default output of the Rnd function), you would use a statement similar to this...

GenerateValues 100, "F", 3

which will place those random values in F3 to F102. Note, you can also use the column's numerical designation in place of its letter designation if you want...

GenerateValues 100, 6, 3

Rick

 

[Diogo]

Looking at the code I might have been a bit simple in my explainations.

The cell (C26) whose value I want to copy one hundred times is the result of
a sum of several other cells whose values are calculated by the rand
function.

I just want to copy that cell 100 times to a column.

What I need is a automated process that mimics me punchin the F9 button,
going to the C26 cell and copying it to for example column F row 3, and
repeat this process 99 more time so at the end I can see how many values
obtained are grater then a certain value. This I can do manualy, of course.

 

[Rick Rothstein \(MVP - VB\)]

Does this do what you want?

Sub GenerateValues(NumberOfTimes As Long, ColumnLocation As Variant, _
StartRow As Long)
Dim X As Long
Const CopyCell As String = "C26"
For X = 1 To NumberOfTimes
Cells(StartRow + X - 1, ColumnLocation).Value = Range(CopyCell)
Application.Calculate
Next
End Sub

Rick

 

[Diogo]

Do I record this in a macro?

 

[Rick Rothstein \(MVP - VB\)]

It depends on how you want to "kick it off" (you didn't tell us that). Create a macro like this...

Sub GetMyRandomNumbers()
GenerateValues 100, "F", 3
End Sub

and execute it by pressing Alt+F8 from your spreadsheet. Or you can add a CommandButton to your spreadsheet and call the macro from its Click event like this...

Private Sub CommandButton1_Click()
GenerateValues 100, "F", 3
End Sub

Or you can "kick it off" from a number of other different ways... it kind of depends on what you want to do.

Rick

 

[Diogo]

Rick

It works for the exeception that it doesn't recalculate the value of the
cell "C26" it copies the same value 100 times.
I need it to punch F9 (Calculate), before a new copy.

 

[Diogo]

Forget it
My mistake
It works fine.

Thanks a lot.