=RAND() Function use question

[Desu Nota from Columbus]

I have the immediate need to generate the integers 1-8 in cells A22-A29 randomly without duplicating numbers.

Is there a way to do this with a formula or does this require a macro?

Also, if it isn't obvious within the solution, how would you change the formula/macro to account for a different range of integers? For instance 1-10, 5-50, etc.

Thanks

 

[sheetspread]

randbetween limits results to a range (check in the analysis toolpak)

/Dim Low As Double
Dim High As Double
Low = 11 '<<< CHANGE AS DESIRED
High = 20 '<<< CHANGE AS DESIRED
R = Int((High - Low + 1) * Rnd() + Low)

 

[VoG]

For random numbers within a range of numbers without repetition try selecting a range and running

Sub rdm()
Dim cell As Range, MyRanRng, x, nDec As Integer
Dim K As Long, iFlag As Boolean
Dim i As Integer, j As Integer, n As Integer
Dim NosAvailable As Long
Dim ArrayOfValues
MyRanRng = Application.InputBox(prompt:="Enter lower and upper limits separated by space", _
    Title:="Enter number range")
If TypeName(MyRanRng) = "Boolean" Then Exit Sub
x = Split(MyRanRng)
If UBound(x) = 0 Then Exit Sub
nDec = Application.InputBox(prompt:="No. decimal places", Title:="Enter decimal places", Type:=1)
If TypeName(nDec) = "Boolean" Then nDec = 0
K = Selection.Cells.Count
NosAvailable = (x(UBound(x)) - x(LBound(x))) * 10 ^ nDec + 1
If K > NosAvailable Then
    MsgBox prompt:="Cells available:" & vbTab & K & vbCrLf & "Numbers available:" & _
        vbTab & NosAvailable & vbCrLf & vbCrLf & _
        "Select a smaller range or increase the number range", _
        Title:="Error trap!", Buttons:=vbOKOnly + vbCritical
    Exit Sub
End If
Randomize
ReDim ArrayOfValues(1 To K) As Variant
For i = 1 To K
    Do
        iFlag = False
        ArrayOfValues(i) = Round(Rnd() * (x(UBound(x)) - x(LBound(x))) + x(LBound(x)), nDec)
        For n = 1 To i - 1
            If ArrayOfValues(i) = ArrayOfValues(n) Then iFlag = True
        Next n
    Loop Until iFlag = False
Next i
j = 0
For Each cell In Selection
    j = j + 1
    cell.Value = ArrayOfValues(j)
Next cell
End Sub

 

[Desu Nota from Columbus]

Thanks a bunch both of you.

Vog, I will undoubtedly have some questions once I get to dissect this code (just giving you fair warning).

 

[mikerickson]

This UDF should do what you want.
Select A22:A29 and enter the array formula =RndValues(8)
(This should be entered with Ctl-Shift-Enter or Cmd+Return for Mac)

Function RndValues(Optional Size As Long, Optional LowValue As Long = 1) As Variant
    Dim Result As Variant
    Dim i As Long, randIndex As Long, temp As Long
    Application.Volatile
    If Size < 1 Then
        Size = Application.Caller.Cells.Count
    End If
    
    ReDim Result(1 To Size)
    For i = 1 To Size
        Result(i) = i + LowValue - 1
    Next i
    
    For i = 1 To Size
        randIndex = Int(Rnd() * Size) + 1
        temp = Result(i)
        Result(i) = Result(randIndex)
        Result(randIndex) = temp
    Next i
    
    RndValues = Result
End Function

For values from 1-10, use the formula =RndValues(10)
For values from 5-50, use the formula =RndValues(46, 5)