How to generate inverted normal random numbers in VBA?

Question

I am trying to generate 5.000 random variables using these excel functions: normsinv(rand()). This is the code I wrote:

Dim r As Integer
r = 1
For r = 1 To 5001

.Cells(r + 1, 1) = Application.WorksheetFunction.NormSInv(Rnd())

r = r + 1

Next

However, when I execute nothing happens (but it does not accuse any error as well)... Could you please tell me what is wrong with the code?

Answer 1

You do not need a loop:

Sub dural()
    Dim r As Range
    Set r = Range("A1:A5001")
    r.Formula = "=normsinv(rand())"
    r.Copy
    r.PasteSpecial (xlPasteValues)
End Sub