Excel VBA fill 100 x 100 martix based on value in a column <p>I would love to fill a spreadsheet with 100 columns and 100 rows. I used the analysis toolpack to create a column of 10,000 numbers from a distribution (using the discrete distribution options 1 variable, 10,000 numbers and the two columns below as values). </p> <p>I want the columns to simulate 100 trials of 100 years using the column as the reproduction rates. The column created simulates the reproduction rates for each cell within the 100 x 100 matrix. I would like the initial row value for all columns to be 31. </p> <p>For the first column, I would like the (2,1) to multiple 31 by the first value in the reproduction column. For (3,1) I would like the to multiple (2,1) by the second value in the reproduction column. I would like this to continue for all 100 cells in the first column. I would then like it to start over on the next column starting with (2,1) being 31 multiplied by the 101 value in the reproductive column. </p> <pre><code>Private Sub Code() Dim i As Long Dim j As Long Dim iCol Dim ws As Worksheet 'Set this to the source sheet (where your trial counts are) Set ws = ActiveWorkbook.Sheets(4) For i = 2 To 100 iCol = ws.Cells(i, 1) For j = 1 To 100 Cells(1, i).Value = 34 For k = 1 To iCol Cells(j + 1, i + 1).Value = Cells(j, i).Value * k Next k Next j Next i End Sub </code></pre> <p>Here is the distribution that I would like the random numbers to be generated from. If there is a way to use the analysis tool within the VBA to allow so the numbers are generated there instead of externally, that would be great!</p> <pre><code>Value, Probability -0.15,0 -0.125,0 -0.1,0.05 -0.075,0 -0.05,0.05 -0.025,0 0,0 0.025,0.05 0.05,0 0.075,0 0.1,0.15 0.125,0.15 0.15,0.3 0.175,0.05 0.2,0.15 0.225,0.05 0.25,0 </code></pre> <p>Any help or advice is greatly appreciated!</p> <p>Thank you!!</p>