Recording Multiple Function Results

Originally Posted by zealot

Hello there!

I wonder if you can help me to make a macro which will copy and paste into a separate sheet a whole column (named “Results” in the attached sheet) of function results. I need this macro to recalculate the whole column a certain number of times (100 or 1000) - each time copying the recalculated column to a separate sheet (named “Output”). So that when there are 50 recalculations there will be 50 separate columns of results stored on the Output sheet. The recalculation can be simulated by a simple function like =0+x with x going from 0 to 500 (the 500 being the number of required recalculations).

I attached the spreadsheet with the calculations…

Hoping to hear any kind of feedback.))

Hi,

If you need this "100 - 1000 times" would you not be better to fill column E with the different controls and let the number of occurrences be performed according to the data in E ? - running a macro 1,000 times will get less interesting with each passing day.

(note, the macro can fill the formula for column C)

there is, of course, a maximum of 256 columns per sheet, how do you want the breaks to occur?

---

Originally Posted by zealot

Hello there!

I wonder if you can help me to make a macro which will copy and paste into a separate sheet a whole column (named “Results” in the attached sheet) of function results. I need this macro to recalculate the whole column a certain number of times (100 or 1000) - each time copying the recalculated column to a separate sheet (named “Output”). So that when there are 50 recalculations there will be 50 separate columns of results stored on the Output sheet. The recalculation can be simulated by a simple function like =0+x with x going from 0 to 500 (the 500 being the number of required recalculations).

I attached the spreadsheet with the calculations…

Hoping to hear any kind of feedback.))

Try

Please Login or Register  to view this content.

Let me know how it goes.
---

Originally Posted by zealot

thanks!!! works like magic on the sheet that I attached. However, when i try to use it on my main research workbook - after I have replaced all the sheet and cell names - it copies the range but with errors....looks like the problem has to do with the fact that there is a reference to a function in the macro:


Range("H3:H" & iLastRowD).Formula = "=E80+D3" & iRow

Do you think you can edit this macro in such a way that it copies the results of the calculations in a certain number of cells, whithout taking into account any of the formulas which generate the results?

Thank you for your time!!!

Hi,

the original was tested on columns A B etc, and incorrectly setup for you.

Dim iRow As Long, iLastRowB As Long, iLastRowE As Long, iCtr As Long

should be

Dim iRow As Long, iLastRowD As Long, iLastRowE As Long, iCtr As Long

the two lines
iLastRowD = ActiveSheet.Range("B65536").End(xlUp).Row
iLastRowE = ActiveSheet.Range("E65536").End(xlUp).Row
need to be
iLastRowD = ActiveSheet.Range("D65536").End(xlUp).Row
iLastRowE = ActiveSheet.Range("E65536").End(xlUp).Row
and can then operate correctly.

hth
---