0

The assignment requires me to run the Monte Carlo result 1000 times. I already create a row of 30 years values(B5:AE5), and I want to repeat the process 1000 times. Every time, there will be a new row comes out, and all the values will be random.

Below is my code, for some reason, it will go to the very bottom of my excel sheet. I want the second row of 30 years values inside (B6:AE6). 

Sub Macros()
Dim trail As Long
trail = InputBox("Enter the number of time you want to simulate this Macros", "Macros", "10")

For i = 1 To trail
Application.CutCopyMode = False
Range("B5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.End(xlDown).Select
Selection.Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
       SkipBlanks:=False, Transpose:=False
Range("A4").Select
Selection.End(xlDown).Select
Selection.Copy
Selection.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMod = False
Next i
Range("B4").Select
End Sub

Thank you sooo much!

Sijie Shao
  • 41
  • 1
  • 8

2 Answers2

0

To answer your question about why your End(xlDown) takes you to the end of the sheet, the Selection.End(xlDown).Select is similar to pressing Ctrl+Down on the spreadsheet. (Likewise Selection.End(xlToRight)).Select is similar to pressing Ctrl+Right.)

Hence if you are on an empty sheet, or if all the cells beneath the active (or referenced) cell are empty, then pressing Ctrl+Down will bring you to the last row.

All that said, you can avoid that whole issue and improve your code significantly by

  • Removing all the Select statements and work directly with the range objects.
  • Using the defined range (B5:AE5) since you know what it is.
  • Just using the counter to resize the range to to paste the values and formats (and eliminate the loop).

See the code below:

Sub Macros()

Dim trail As Long
trail = InputBox("Enter the number of time you want to simulate this Macros", "Macros", "10")

With Range(Range("B5"), Range("AE5"))
     .Copy
     .Offset(1).Resize(trail - 1, 30).PasteSpecial xlPasteValues
     .Offset(1).Resize(trai1 - 1, 30).PasteSpecial xlPasteFormats
End With

With Range("A5")
     .Copy .Offset(1).Resize(trail - 1)
End With

'if you don't need to copy the formats you can change the above With statements to just this:

'With Range("A5:BE5")
'     .Offset(i).Resize(trail - 1,31).Value = .Value
'End With

End Sub
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • Hi Scott. Thank you so much for your answer. It actually have values appears. But the problem is It is the same value for all the roles. What I want is for each row, there are new values. Every time when I refresh the excel, The values inside B5:AE5 will changes. Basically, I want to record 1000 times of the B5:AE5 values. Each row should be different. – Sijie Shao Nov 25 '15 at 05:24
  • so new random values for each row? is there an upper / lower limit to the values? or do you have some sort of *definition* to the randomness? – Scott Holtzman Nov 25 '15 at 05:28
  • Yes, different random value each row. There are another table I created. With IF and RNAD function, every time I refresh the sheet, 30 interest rates will be generate( between 3%-8%), those interest rate will calculate 30 NPV. I copy those 30 NPV to the new table as first role. By using the VBA code, I want to have 1000 row of those 30 NPV. Every role will be different NPV. Can you understand what I talked about? It may be a little bit messy. – Sijie Shao Nov 25 '15 at 05:39
  • @SijieShao - It's still a little unclear. If you want 1000 rows of random numbers then the `InputBox` to set trail is not needed correct? For the 1000 rows of 30 random NPVs can you just use the `RAND` Function or `RANDBETWEEN`? – Scott Holtzman Nov 25 '15 at 13:27
0

It sounds like you want to place formulas in the selected number of rows.

Sub Frmla()
    Dim i As Long
    i = InputBox("enter Number")
    Range("B6:AE" & 5 + i).FormulaR1C1 = "=R[-1]C*0.7"'whatever the formula is

End Sub
Davesexcel
  • 6,896
  • 2
  • 27
  • 42