Creating a table of random numbers

M

matt3542

Dear Forum,

I am relatively new to VBA programming and as such would very much
appreciate any help with regards to writing VBA code that would allow me to
create a loop to compile a X (where x represents the number of columns
provided by a user via an inputbox) by Y (where y represents the number of
rows, again provided by an input box) size table with each cell having a
random value in it where the range of random numbers is between 1 and the
total number of cells. I have been tinkering with the notation -
ActiveCell.FormulaR1C1 = Int((TOTAL NUMBER OF CELLS * Rnd) + 1) but have not
managed to get it to work. Can anyone suggest a solution that uses the above
notation as I would like to understand more and have other areas of work that
I could apply its principles to.

Any help gratefully received
Regards
Matt




- Create a new workbook
- Setup an InputBox to ask the user for their required number of columns for
a table of data (x)
- Setup an InputBox to ask the user for their required number of rows for a
table of data (y)
- Setup a loop to compile a x by y size table with each cell having a random
value in it where the range of random numbers is between 1 and the total
number of cells (tip: ActiveCell.FormulaR1C1 = Int((TOTAL NUMBER OF CELLS *
Rnd) + 1)
 
G

Gary''s Student

Once the range has been selected:

Sub hfakdf()
For Each r In Selection
r.Formula = "=int(Rand() *" & Selection.Count & "+1)"
Next
End Sub
 
J

Jim Cone

This approach requires the installation of the AnalysisToolPak...
'----
Sub RandomByChoice()
Dim rng As Range
Dim x As Long
Dim y As Long
Dim N As Long

x = 10
y = 5
N = x& * y&

Set rng = ActiveCell.Resize(y, x)
rng.Formula = "=RandBetween(1," & N & ")"
rng.Value = rng.Value
Set rng = Nothing
End Sub
'----
You may also want to take a look at my "Special Randoms" workbook.
It can be downloaded at http://excelusergroup.org/media/
--
Jim Cone
Portland, Oregon USA



"matt3542"
wrote in message
Dear Forum,
I am relatively new to VBA programming and as such would very much
appreciate any help with regards to writing VBA code that would allow me to
create a loop to compile a X (where x represents the number of columns
provided by a user via an inputbox) by Y (where y represents the number of
rows, again provided by an input box) size table with each cell having a
random value in it where the range of random numbers is between 1 and the
total number of cells. I have been tinkering with the notation -
ActiveCell.FormulaR1C1 = Int((TOTAL NUMBER OF CELLS * Rnd) + 1) but have not
managed to get it to work. Can anyone suggest a solution that uses the above
notation as I would like to understand more and have other areas of work that
I could apply its principles to.

Any help gratefully received
Regards
Matt




- Create a new workbook
- Setup an InputBox to ask the user for their required number of columns for
a table of data (x)
- Setup an InputBox to ask the user for their required number of rows for a
table of data (y)
- Setup a loop to compile a x by y size table with each cell having a random
value in it where the range of random numbers is between 1 and the total
number of cells (tip: ActiveCell.FormulaR1C1 = Int((TOTAL NUMBER OF CELLS *
Rnd) + 1)
 
M

matt3542

Hi Gary, any chance you could give the code in full as I am still struggling,
I'm really new to all this, many thanks. Matt
 
M

matt3542

Apologies Jim but upon closer inspection although the dimensions of the table
are fine, my aim was to create a table of data with random numbers where the
range of random numbers is between 1 and the total number of cells with NO
duplicates. I have used your code below and it does work but it duplicates
values, i.e if I specify a table 5 * 5 I would expect it to be populated at
random with the numbers 1 - 25 from A5:E5 with each number appearing only
once. At present this code is returning duplicate values. Is there any way I
can prevent this from occurring?

Sub RandomTable()
Dim rng As Range
Dim x As Long
Dim y As Long
Dim N As Long

x = InputBox("Please enter the number of Columns required for the table")
y = InputBox("Please enter the number of Rows required for the table")
N = x& * y&

Sheets("Sheet1").Select
Range("A1").Select

Set rng = ActiveCell.Resize(y, x)
rng.Formula = "=RandBetween(1," & N & ")"
rng.Value = rng.Value
Set rng = Nothing

End Sub

Many Thanks
Matt
 
J

Jim Cone

Or this...
'--
Sub RandomByChoice_R1()
Dim rng As Range
Dim x As Long
Dim y As Long
Dim N As Long
Dim M As Long
Dim i As Long

x = 5
y = 10
i = x& * y&
Set rng = ActiveCell.Resize(y, x)

For N = 1 To i
Do
M = Int(i * Rnd + 1)
If Len(rng(M).Value) = 0 Then
rng(M).Value = N
Exit Do
End If
Loop
Next
Set rng = Nothing
End Sub
'--
Jim Cone
Portland, Oregon USA




"matt3542"
wrote in message
Apologies Jim but upon closer inspection although the dimensions of the table
are fine, my aim was to create a table of data with random numbers where the
range of random numbers is between 1 and the total number of cells with NO
duplicates. I have used your code below and it does work but it duplicates
values, i.e if I specify a table 5 * 5 I would expect it to be populated at
random with the numbers 1 - 25 from A5:E5 with each number appearing only
once. At present this code is returning duplicate values. Is there any way I
can prevent this from occurring?

Sub RandomTable()
Dim rng As Range
Dim x As Long
Dim y As Long
Dim N As Long

x = InputBox("Please enter the number of Columns required for the table")
y = InputBox("Please enter the number of Rows required for the table")
N = x& * y&

Sheets("Sheet1").Select
Range("A1").Select

Set rng = ActiveCell.Resize(y, x)
rng.Formula = "=RandBetween(1," & N & ")"
rng.Value = rng.Value
Set rng = Nothing

End Sub

Many Thanks
Matt
 
M

matt3542

Thankyou Jim, that worked just right

Jim Cone said:
Or this...
'--
Sub RandomByChoice_R1()
Dim rng As Range
Dim x As Long
Dim y As Long
Dim N As Long
Dim M As Long
Dim i As Long

x = 5
y = 10
i = x& * y&
Set rng = ActiveCell.Resize(y, x)

For N = 1 To i
Do
M = Int(i * Rnd + 1)
If Len(rng(M).Value) = 0 Then
rng(M).Value = N
Exit Do
End If
Loop
Next
Set rng = Nothing
End Sub
'--
Jim Cone
Portland, Oregon USA




"matt3542"
wrote in message
Apologies Jim but upon closer inspection although the dimensions of the table
are fine, my aim was to create a table of data with random numbers where the
range of random numbers is between 1 and the total number of cells with NO
duplicates. I have used your code below and it does work but it duplicates
values, i.e if I specify a table 5 * 5 I would expect it to be populated at
random with the numbers 1 - 25 from A5:E5 with each number appearing only
once. At present this code is returning duplicate values. Is there any way I
can prevent this from occurring?

Sub RandomTable()
Dim rng As Range
Dim x As Long
Dim y As Long
Dim N As Long

x = InputBox("Please enter the number of Columns required for the table")
y = InputBox("Please enter the number of Rows required for the table")
N = x& * y&

Sheets("Sheet1").Select
Range("A1").Select

Set rng = ActiveCell.Resize(y, x)
rng.Formula = "=RandBetween(1," & N & ")"
rng.Value = rng.Value
Set rng = Nothing

End Sub

Many Thanks
Matt
Click to expand...
 
M

matt3542

Hi, Many thanks I eventually got that to work - one slight glitch is the fact
that I didn't want any duplicate values to occur, is there a way the code
below could be amended to get around this problem so that all the numbers
within the range appears only once? Thanks, Matt
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Produce Random Numbers 6
Creating a table based on user input 4
Six columns randon no.'s no dupes 6
Creating a Search using a combo box and then creating a report. 4
Adding sequence numbers to paragraphs 0
random number generation help 3
Word macro that can call cells out of Excel for a find and replace 0
random numbers 7

Top