Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a table based on user input
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a table based on user input
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 he 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a table based on user input
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 he 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a table based on user input
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 he 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a table based on user input
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 he 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table data based on user input | Excel Programming | |||
Creating Input box that grap data based on user answer | Excel Programming | |||
Creating a Directory from a user input | Excel Programming | |||
Help creating link & formula from user input | Excel Programming | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming |