# Random Numbers in 2 columns

#### abee4life

##### Active Member
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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``````

What do you mean by select a range?

Select the cells where you want the random numbers, then run the macro.

Sorry, I've done it now.. thanks

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.

Try like this

=INDEX(B\$1:B\$40,MATCH(C1,A\$1:A\$40,0))

Replies
6
Views
128
Replies
20
Views
606
Replies
5
Views
412
Replies
2
Views
587
Replies
8
Views
355

1,206,711
Messages
6,074,467
Members
446,071
Latest member
gaborfreeman

### 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.

### Which adblocker are you using?

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

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