How to use an input box to enable a range of cells to autofill
Category: [Excel] Demo Available
How to use an input box to enable a range of cells to autofill
Requirement:
The user is trying to generate 1-3 columns of data with random numbers with a selected mean and standard deviation using input boxes.
The user has managed to get the random numbers in 1 column with a chosen mean and standard deviation but cant get how to use input box to feed into the range selection? Also how to enable the extension from 1 to selected columns (choosing different means and standard deviations). The code currently only generates 1 column based on my input boxes (means and SD), and is hard coded to 10 rows.Any help much appreciated 
Code:
Sub getdata()
Dim myValue As Variant
Dim myValue2 As Variant
Dim myValue3 As Variant
myValue = InputBox("set mean value in column 1")
myValue2 = InputBox("set SD value in column 1")
myValue3 = InputBox("set range of cases in column 1")
range("C1").Value = myValue
range("D1").Value = myValue2
range("E1").Value = myValue3
range("A1").Select
ActiveCell.FormulaR1C1 = "=NORMINV(RAND(),R1C3,R1C4)"
Selection.AutoFill Destination:=range("A1:A10"), Type:=X1FillDefault
range("A1:A10").Select
range("A10").Select
End Sub
Solution:
Code:
Sub GenerateDistributions()
Dim myValue1 As Double, myValue2 As Double, myValue3 As Double
myValue1 = InputBox("Set Mean value")
myValue2 = InputBox("Set Standard Deviation value")
myValue3 = InputBox("Set Number of Rows")
[E1] = myValue1: [F1] = myValue2: [G1] = myValue3
Range("A1:C1").FormulaR1C1 = "=NORMINV(RAND(),R1C5,R1C6)"
Range("A1:C" & myValue3).FillDown
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Carim.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to set up an autofill macro |
| How to delete a row if cell in range contains specific text |
| How to use a macro to clear cells in range with condition for each row |
| How to use VBA code to check a range of cells for a text |
| How to find a piece of text inside cells in a range and insert a line break on its left |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Official casino website available for gamblers from Australia. The casino attracts new pokies with high payout percentages . Playing for real money is easier with bonuses, but the casino does not offer many promotions. Therefore, you need to consider options as you accrue deposits.