0

I have formulas in F15:F1649 that, when auto calc is on, the values refresh every time with a mouse click (e.g., there is a RAND() function in every cell in F15:F1649). I am trying to run a monte carlo simulation that copies the dynamic data in F15:F1649, and pastes it as values to G15:G1649, then copies again and pastes the refreshed data to H15:H1649 and so on until 1000 trials are completed.

I have recorded a macro myself, but the code is pretty sloppy. Please see below for a few example lines of the code:

Sub Monte_Carlo_Sim()
'
' Monte_Carlo_Sim Macro
'

'
    Range("F15").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.End(xlUp).Select
    Range("G15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("H15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("I15").Select
RedYeti
  • 1,024
  • 14
  • 28
sunakt
  • 1
  • I am not sure what the actual question is here? Does this code work or not? What are you needing to know? – RedYeti Dec 16 '20 at 16:47
  • The code works, however it is pretty cumbersome to have to copy and paste that range as values to 1000 columns manually in order to record the macro. In addition, I can only do ~700 trials as of now, as I believe my lines of code may reach the max that VBA allows? – sunakt Dec 16 '20 at 16:54
  • This question seems quite easy if you have programming expertise. I'm guessing you don't have such? Whether you have such matters. If you don't have expertise, it could be wise to use a solution that doesn't confuse you much, so that you can tinker with it later on, on your own. However, such solution might be inefficient from a programming perspective. – Mark Fernandes Dec 16 '20 at 17:43
  • Hi Mark -- You're right I do not have any programming expertise haha. I am just looking for a more efficient macro for the data set. – sunakt Dec 16 '20 at 19:07

1 Answers1

0

You already have most of your code generated using the macro recorder. If you have a basic understanding of programming, you can add small changes to it and get your desired result.

The problem in your code is that you are manually selecting the location where the copied data needs to be pasted. Instead of doing that you need let the code identify the last empty column to paste the values and then loop it 1000 times like below:

For i = 1 To 5
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.End(xlToRight).Offset(0, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Next i

The following code: Selection.End(xlToRight).Offset(0, 1).Select identifies the last non-filled column

Note: In the For loop above, you change the value from 5 to your desired number to loop the code that many times. But test this with 5 first and see if its working as expected.

Gangula
  • 5,193
  • 4
  • 30
  • 59
  • An important note: generally answers in the VBA/Excel tagspace [avoid `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Dec 16 '20 at 18:57
  • Thank you, Gangula. Please forgive me for my lack of coding knowledge, but I pasted your recommended code in VBA and ran it, but it only pasted that range of data in the last empty column. Unfortunately, it did not paste the range as values in the 1000 columns before the last column, as I am desiring it too. Any suggestions? – sunakt Dec 16 '20 at 19:15
  • I updated the code above and added a for loop, check if that's what you're expecting – Gangula Dec 17 '20 at 09:35
  • Thank you so much, Gangula. It did refresh the equation 5 times, but it only pasted the final values in 1 column. What I am desiring to do is: copy a range of RAND() functions, paste them in the column to the right. Then refresh the RAND() function again, and then paste those values in another column to the right, and so on. The final outcome should be, for each RAND() function in a row, there should be 1000 pasted values in the columns to the right. Does that make sense? – sunakt Dec 17 '20 at 17:35
  • It does not paste the values in one column when I tried. I'll be able to help you if you can share how your data is structured. – Gangula Dec 18 '20 at 06:12