Random Numbers in 2 columns

abee4life

Active Member
Joined
Jan 27, 2005
Messages
319
Hi,

I need to get random numbers to appear down seperate columns, without doubling up.

I have 80 numbers to fit into 80 cells.

I can not let them be used more than once.

I need to create random numbers from C1-C40 and again from F1-F40.

I can use VBA.

Also, once this is done, I need the cells in D1-D40 and E1-E40 to lookup Column A to find the number and display what is written in Column B. As in, what is if 13 came out for C1, then I'd need what is written in B13 to go into D1. (this is where 1-80 are in order from A1-A80)...

Does this make sense?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
This may be a start: select a range then run the macro

Code:
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 Not IsArray(x) 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
 
Upvote 0
Can someone help with the lookup please?

When the number appears in the box, I want it to then lookup the word that is associated with it.
 
Upvote 0

Forum statistics

Threads
1,226,390
Messages
6,190,736
Members
453,616
Latest member
nathancook

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top