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

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 wrote:
>
> 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

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

"Dave Peterson" wrote:

> 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 wrote:
> >
> > 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
>

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

"Dave Peterson" wrote:

> 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 wrote:
> >
> > 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
>

Glad you got it working!

matt3542 wrote:
>
> Ps I forgot to mention the most important thing..the solution worked perfectly
> Thanks
> Matt
>
> "Dave Peterson" wrote:
>
> > 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 wrote:
> > >
> > > 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
> >

--

Dave Peterson