Random Number Generator For Each Cell in a Selected Range

[Tanner_2004]

This code works great, but it stops after producing 10 random numbers. Can someone please tell me how to modify so that it produces a random number for every cell within an selected range? Thank you.

Sub randomNumbers()    Low = Application.InputBox("Enter first valid value", Type:=1)
    High = Application.InputBox("Enter last valid value", Type:=1)
    Selection.Clear
    For Each cell In Selection.Cells
        If WorksheetFunction.CountA(Selection) = (High - Low + 1) Then Exit For
        Do
            rndNumber = Int((High - Low + 1) * Rnd() + Low)
        Loop Until Selection.Cells.Find(rndNumber, LookIn:=xlValues, lookat:=xlWhole) Is Nothing
        cell.Value = rndNumber
    Next
End Sub

 

[Eric W]

Maybe:

Sub randomNumbers()

    Low = Application.InputBox("Enter first valid value", Type:=1)
    High = Application.InputBox("Enter last valid value", Type:=1)
   
    For Each cell In Selection.Cells
        cell.Value = Int((High - Low + 1) * Rnd() + Low)
    Next

End Sub

The original code had a check to make sure that no random number was repeated. I can add that if you want, but it would depend on the selection size.

 

[JoeMo]

What are your High and Low inputs, and how many cells in your Selection when the code stops at 10?

 

[Tanner_2004]

Eric. Worked beautifully! Thank you.

 

[Eric W]

You're welcome!

If you do ever want the "no repeats" version, try:

Sub randomNumbers()
Dim Low As Long, High As Long, UsedNums As Object, work As Long


    Set UsedNums = CreateObject("Scripting.Dictionary")
    
    Low = Application.InputBox("Enter first valid value", Type:=1)
    High = Application.InputBox("Enter last valid value", Type:=1)
    
    If Selection.Cells.Count > (High - Low) + 1 Then
        MsgBox "Not enough cells for unique random numbers."
        Exit Sub
    End If
   
    For Each cell In Selection.Cells
        Do
            work = Int((High - Low + 1) * Rnd() + Low)
        Loop Until Not UsedNums.exists(work)
        UsedNums.Add work,1
        cell.Value = work
    Next


End Sub

 

[Tanner_2004]

Thanks agian!!!!!!!!!!

 

[Tanner_2004]

Would you know how to enter a random percentage? I changed tge type to 14, but it gives me 0 or 100%. Please and thank you.

 

[Eric W]

On the first macro, try

cell.Value = (Int((High - Low + 1) * Rnd() + Low)) / 100

On the second, try:


cell.Value = work / 100

and make sure to format the cells as Percentage.</pre>

 

[Tanner_2004]

Thank you Eric.