none
How to loop formulas not values? RRS feed

  • Question

  • I'm trying to produce 81 rows and 1000 columns of values produced by this code. 

    Sub Button1_Click()
    Dim i As Integer
    Dim nloops As Integer
    Dim Drift As Integer
    Dim Variance As Integer
    Dim dt As Double
    Dim ntimesteps As Integer
    Dim InitialValue As Integer
    Dim timestep As Integer
    Drift = 0.513
    Variance = 0.36
    ntimesteps = 81
    InitialValue = 0.51

    dt = 1

    dz = Application.WorksheetFunction.NormSInv(Rnd())

    nloops = 1000
    For i = 1 To nloops
        For timestep = 0 To ntimesteps
            
            Cells(timestep, i).Value = InitialValue + InitialValue * Drift + InitialValue * Variance * * dt ^ 0.5
            
            Next timestep
        Next i

    End Sub

    But instead of producing different values in each cell, it copies the first value generated? How can I loop the formula in each cell?


    Thursday, December 4, 2014 8:57 AM

All replies