Problem getting VBA to recalculate and store results within a loop then outputting results

Hi Experts, I'm a novice at VBA and am trying some simple code to generate some random numbers, store the results in an array and then output them on another sheet. Not sure if it's not recalculating each time or if it's the way I'm outputting the results but I get 100 columns of identical numbers instead of 100 columns of "random" numbers. Would appreciate any help! Here is the code:

Option Explicit
Option Base 1
Sub myMacro2()

Dim vData As Variant
Dim dOutput() As Variant
Dim j As Long
Dim k As Long

Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual

' Assign the range to a variant containing an array.
vData = Worksheets("Input2").Range("B2:B21")

' Set up the result array.
ReDim dOutput(UBound(vData, 1), 100)

' Generate the numbers.
For k = 1 To 100
With Worksheets("Input2")
.EnableCalculation = False
.EnableCalculation = True
.Calculate
End With
dOutput = vData
Next k

' Return results by assigning the result array to the range.
Worksheets("Results2").Range("B2"). _
Resize(UBound(dOutput, 1), 100) = dOutput

'Application.Calculation = xlCalculationManual
Application.ScreenUpdating = True

Jasper, I attached the file to my prior post - sorry new to site and not too familiar with it yet. Thanks for your help!

The random numbers are just "placeholders" for what the Monte Carlo spreadsheet will calculate and don't represent the actual values. Instead of copying the results each iteration to a results sheet I'm just trying to store the results (20 values) each time the spreadsheet calculates (100 times) into an array and then put all the arrays in the results sheet after all the iterations are done. The current way I'm doing it takes about 2 minutes for 2000 iterations in Excel 2010 (took 40 seconds in Excel 2003) and I'm just trying to speed it up by not copying the results on to the results sheet until everything is finished.

Is this what you wanted?7

Please Login or Register  to view this content.

Jasper,
I'm trying to avoid writing to the sheet with each calculation (iteration) - that is the way it is being done now in the current model. Instead I"m trying to find a way to calculate the sheet, store the results (represented by B2:B21 in the example) in an memory array, recalculate the sheet, store the results in the next column of the memory array, and repeat this for a total of 100 times. So will end up with 100 columns of results that will then be copied (& transposed) on to the results sheet. Doing everything in memory versus all the writing to the results sheet will speed it up a lot but I just can't seem to get the loops to do what I want. Any other suggestions would be greatly appreciated!

Jindon - the application.transpose suggestion did exactly what I wanted for the final write. Now, just need to find away to store each iteration in memory until the count is finished as described above. Thanks for the help!

Sample code

Please Login or Register  to view this content.

Jindon, I like the code but I need to use the calculated results from the input2 sheet for each iteration. I was just using the random numbers as placeholders to test whether I would get new numbers each time - the real calculated results I'm trying to capture at each iteration are not random numbers so I can't use the formula like you inserted but have to capture the results (20 cells) at each iteration for the 100 iterations and then transpose the results and put on the results sheet.
In reply 6 for a similar problem posted on the link below - ChrisM indicates:
1. create a temp array to hold one loop's data (you can't assign directly from the worksheet to a set row of the big array)
2. Each time the loop runs write the results to the temp array
3. Write the temp array row to the big array
4. When all done, write the entire big array to worksheet
http://http://www.mrexcel.com/forum/excel-questions/692324-copy-paste-value-loop-within-visual-basic-applications-array.html
I'm having trouble converting his code to something that will work in my example. Appreciate your help and patience with a newbie like me. Kirk

As I mentioned, it was just a sample, because I don't understand what you are really trying to do.

If the calculation will populate the columns of data in input2 column by column, why not just copy/pastespecial with transpose protocol set to true after 100 columns?

Am I missing something?

The more complicated spreadsheet contains other sheets that feed a master calculation sheet that calculates results. I need to capture those results (say B2:B21) each time the spreadsheet is calculated (say for 100 times). I had previously used vba to capture the results at the end of each calculation and paste the results onto a results sheet but since Excel 2010 is much slower than Excel 2003, was trying to find a way to avoid pasting the results until all 100 recalculations were done. Thus, I am trying to capture the results from those cells (B2:B21) each time the complicated spreadsheet recalculates, store them in an array in memory, and when all 100 recalculations are done and stored - place the results of all 100 recalculations on to a results sheet for additional processing and charting.

Then you need to manage them like

Please Login or Register  to view this content.