lundi 29 août 2016

VBA: Random Numbers Sheet keeps changing in loop

I have a sheet with random numbers and a loop. The problem is: I need to refresh the sheet and recalculate all the numbers in the sheet. However, the random numbers should only change once in the loop. Right now, they keep changing all the time and the code does not finish. Does anybody know, how I can refresh the random numbers only once per loop? Any help is greatly appreciated.

  Sub MonteCarlo()
  Application.ScreenUpdating = False
   Dim x As Integer

Application.Calculation = xlManual

        For x = 1 To 1
        Do
        Worksheets("Ex-Ante TE").Calculate

        DoEvents
        Loop While Not Application.CalculationState = xlDone

     Worksheets("Monte Carlo").Range("A" & x).Value = Worksheets("Ex-Ante Te").Range("B2").Value
    Worksheets("Monte Carlo").Range("B" & x).Value = Worksheets("Ex-Ante Te").Range("B3").Value
    Worksheets("Monte Carlo").Range("C" & x).Value = Worksheets("Ex-Ante Te").Range("B4").Value

        Next

Application.ScreenUpdating = True
  Application.Calculation = xlAutomatic

End Sub




Aucun commentaire:

Enregistrer un commentaire