Creating a table based on user input

M

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
 
D

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.)
 
M

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
 
M

matt3542

Ps I forgot to mention the most important thing..the solution worked perfectly
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

Top