Hi

I am trying to simulate a dice roll with: =RAND()*(6-1) which works fine.
I.E. in Cell A1: =RAND()*(6-1)

The problem I have that I want to store the first result in the next cell
I.E. in cell A2 result of A1 but
every time I click on the button to roll the dice A1 changes which is ok -
but A2 does not keep the result of the first roll. This is my problem. Is
there any way round this?

I hope you get my drift.

Thanking you all in anticipation.

Paul

First, I would think your formula would look more like:

=INT(RAND()*(6-1+1)+1)

VBA's help for the RND function shows this to get a random number between two
numbers.

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

And since you're clicking a button to fill that cell, I think I'd have the macro
do the work.

This puts an integer between 1 and 6 in the next available cell in column A. It
puts it as a value--not a formula.

Option Explicit
Sub testme()
Dim myCell As Range

With ActiveSheet
Set myCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

'Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
myCell.Value = Int((6 - 1 + 1) * Rnd + 1)
'if you want the formula that will change when you recalculate:
'myCell.Formula = "=INT(RAND()*(6-1+1)+1)"

End Sub

Paul T wrote:
>
> Hi
>
> I am trying to simulate a dice roll with: =RAND()*(6-1) which works fine.
> I.E. in Cell A1: =RAND()*(6-1)
>
> The problem I have that I want to store the first result in the next cell
> I.E. in cell A2 result of A1 but
> every time I click on the button to roll the dice A1 changes which is ok -
> but A2 does not keep the result of the first roll. This is my problem. Is
> there any way round this?
>
> I hope you get my drift.
>
> Thanking you all in anticipation.
>
> Paul

--

Dave Peterson