Displaying results of loop in cells
 

I have very little experience with VBA in Excel and I'm trying to do a
Monte Carlo simulation. I have the following:


For x = 1 To 10000
Calculate
Range("D2").Select
Selection.Copy
Range("D6").Select
Selection.PasteSpecial Paste:=xlPasteValues
Next x

D2 is the cell where the result from another worksheet is displayed.
This result is derived from some random variables, and after each
calculation the result is different.

D6 is the first cell I want the result from D2 to be copied after the
first calculation, and D7 the result after the second calculation, etc.
etc.

If I change the 5th line with:

Range("D6:D10005").Select

I get the same result 10000 times, and not 10000 different results as I
want. I probably need to use the ARRAY function, but I have no idea how
to use this.

Displaying results of loop in cells
 

Here is what you want... Based on what you want I hope I am not helping you
cheat on your homework. It sounds like a lot of the assignments I got in
school... :)

Sub test()
Dim x As Long
Dim rngPaste As Range

Set rngPaste = Range("D6")

For x = 1 To 10000
Calculate
Range("D2").Copy
rngPaste.PasteSpecial Paste:=xlPasteValues
Set rngPaste = rngPaste.Offset(1, 0)
Next x

End Sub

HTH

" wrote:

I have very little experience with VBA in Excel and I'm trying to do a
Monte Carlo simulation. I have the following:


For x = 1 To 10000
Calculate
Range("D2").Select
Selection.Copy
Range("D6").Select
Selection.PasteSpecial Paste:=xlPasteValues
Next x

D2 is the cell where the result from another worksheet is displayed.
This result is derived from some random variables, and after each
calculation the result is different.

D6 is the first cell I want the result from D2 to be copied after the
first calculation, and D7 the result after the second calculation, etc.
etc.

If I change the 5th line with:

Range("D6:D10005").Select

I get the same result 10000 times, and not 10000 different results as I
want. I probably need to use the ARRAY function, but I have no idea how
to use this.

Displaying results of loop in cells
 

Thanks, it works, great

And about homework, I'm past that age. I wish I had assignments like
this on my school, it would have saved me a lot of time with the things
I want to do with Excel right now.