Creating a table based on user input

[matt3542]

Hi Forum,

As always I would be very grateful if anyone can help with writing some
Excel VBA code that would allow me to complete the following task;

I have 2 input boxes that prompt the user to specify the value of two
variables (x = the required number of columns & y = the required number of
rows for a table of data. I would then like to create 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. I am really new to
VBA but have been told the code would involve the statement
ActiveCell.FormulaR1C1 = Int((TOTAL NUMBER OF CELLS * Rnd) + 1) but I am
struggling to make it work. I'd be really appreciative if anyone is willing
to take the time to help with the code for this loop.

Best wishes
Matt

 

[Dave Peterson]

Did you want to make sure that the table of random numbers had no duplicates?

I'm guessing that duplicates should not be allowed.

If that's true, visit J.E. McGimpsey's site and grab a copy of his =RandInt()
function.
http://www.mcgimpsey.com/excel/udfs/randint.html

Then add that to your project.

Then create a sub that uses that function:

Option Explicit
Sub testme01()

Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(_
Prompt:="Please select a range for the table", _
Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub 'user hit cancel
End If

With myRng
.FormulaArray = "=randint()"
.Value = .Value
End With

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[matt3542]

Dear Dave,

Thankyou so much for taking the time to help me, I found your reply
extremely helpful and it well exceeded my expectations. I am overwhelmed by
your selfless generosity in sharing your expertise and in pointing me in the
right direction towards learning materials that will undoubtedly help to
further my understanding.

Have a great weekend and thankyou again
Matt

 

[matt3542]

Ps I forgot to mention the most important thing..the solution worked perfectly
Thanks
Matt

 

[Dave Peterson]

Glad you got it working!

Ps I forgot to mention the most important thing..the solution worked perfectly
Thanks
Matt