I am using excel 2007.

I have a range of cells in sheet2. The range is A1:E300. The cells
contain numbers. Some cells have the same numbers in them.

I have 3 buttons on a sheet.

The first button put a value from a random cell on sheet2 into cell A9
on sheet 1.
Here is the code for button 1

Private Sub Number1_Click()

Sheet1.Range("A9").Value = Sheet2.Cells(Int(Rnd * 300) + 1, Int(Rnd * 5)
+ 1)

End Sub


The second button puts a value from a random cell on sheet2 in B9 on
sheet 1 but I do not want A9 to equal B9. This seems to work.

Here is my code

Private Sub Number2_Click()

Do

Sheet1.Range("B9").Value = Sheet2.Cells(Int(Rnd * 300) + 1, Int(Rnd * 5)
+ 1)

Exit Do

Loop Until Sheet1.Range("A9").Value < Sheet1.Range("B9").Value

End Sub


The third button puts a value from a random cell on sheet2 in C9 on
sheet 1. But I do not want C9 to equal A9 and B9. This part does not work.

Here is my code

Do

Sheet1.Range("C9").Value = Sheet2.Cells(Int(Rnd * 300) + 1, Int(Rnd * 5)
+ 1)

Exit Do

Loop Until Sheet1.Range("C9").Value < Sheet1.Range("B9").Value And
Sheet1.Range("C9").Value < Sheet1.Range("A9").Value

End Sub


Any help would be greatly appreciated

sc

Steve,

You have the right idea with Do Loops but the Exit Do is stopping the code
looping prematurely. Remove the line

Exit Do

from buttons 2 & 3 and they will work fine.

Mike

"Steve" wrote:

I am using excel 2007.

I have a range of cells in sheet2. The range is A1:E300. The cells
contain numbers. Some cells have the same numbers in them.

I have 3 buttons on a sheet.

The first button put a value from a random cell on sheet2 into cell A9
on sheet 1.
Here is the code for button 1

Private Sub Number1_Click()

Sheet1.Range("A9").Value = Sheet2.Cells(Int(Rnd * 300) + 1, Int(Rnd * 5)
+ 1)

End Sub


The second button puts a value from a random cell on sheet2 in B9 on
sheet 1 but I do not want A9 to equal B9. This seems to work.

Here is my code

Private Sub Number2_Click()

Do

Sheet1.Range("B9").Value = Sheet2.Cells(Int(Rnd * 300) + 1, Int(Rnd * 5)
+ 1)

Exit Do

Loop Until Sheet1.Range("A9").Value < Sheet1.Range("B9").Value

End Sub


The third button puts a value from a random cell on sheet2 in C9 on
sheet 1. But I do not want C9 to equal A9 and B9. This part does not work.

Here is my code

Do

Sheet1.Range("C9").Value = Sheet2.Cells(Int(Rnd * 300) + 1, Int(Rnd * 5)
+ 1)

Exit Do

Loop Until Sheet1.Range("C9").Value < Sheet1.Range("B9").Value And
Sheet1.Range("C9").Value < Sheet1.Range("A9").Value

End Sub


Any help would be greatly appreciated

sc

Mike H wrote:
Steve,

You have the right idea with Do Loops but the Exit Do is stopping the code
looping prematurely. Remove the line

Exit Do

from buttons 2 & 3 and they will work fine.

Mike

"Steve" wrote:

I am using excel 2007.

I have a range of cells in sheet2. The range is A1:E300. The cells
contain numbers. Some cells have the same numbers in them.

I have 3 buttons on a sheet.

The first button put a value from a random cell on sheet2 into cell A9
on sheet 1.
Here is the code for button 1

Private Sub Number1_Click()

Sheet1.Range("A9").Value = Sheet2.Cells(Int(Rnd * 300) + 1, Int(Rnd * 5)
+ 1)

End Sub


The second button puts a value from a random cell on sheet2 in B9 on
sheet 1 but I do not want A9 to equal B9. This seems to work.

Here is my code

Private Sub Number2_Click()

Do

Sheet1.Range("B9").Value = Sheet2.Cells(Int(Rnd * 300) + 1, Int(Rnd * 5)
+ 1)

Exit Do

Loop Until Sheet1.Range("A9").Value < Sheet1.Range("B9").Value

End Sub


The third button puts a value from a random cell on sheet2 in C9 on
sheet 1. But I do not want C9 to equal A9 and B9. This part does not work.

Here is my code

Do

Sheet1.Range("C9").Value = Sheet2.Cells(Int(Rnd * 300) + 1, Int(Rnd * 5)
+ 1)

Exit Do

Loop Until Sheet1.Range("C9").Value < Sheet1.Range("B9").Value And
Sheet1.Range("C9").Value < Sheet1.Range("A9").Value

End Sub


Any help would be greatly appreciated

sc



Thanks. That did the trick.