VBA- Why End(xlDown) will take me to the very bott

Question

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!

Answer 1

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

Answer 2

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