I'm a R programmer but am attempting VBA for the first time. I have a pretty simple population projection based on a series of randomly selected birth and survival values. I have predicted population values for years 1 to 20 in cells BC4:BC23 in Sheet1. Every time I refresh, the values change. I would like to refresh 100 times and store each of the population values into Sheet2 (100 columns with 20 values).
我是一个R程序员，但是我第一次尝试VBA。我有一个非常简单的人口预测基于一系列随机选择的出生和生存值。我已经预测了1到20年的细胞数量值BC4:BC23在Sheet1。每次刷新时，值都会改变。我想要刷新100次，并将每个种群值存储到Sheet2(包含20个值的100列)中。
Based on my internet searching, it seems that a combination of a loop and EnableCalculation is a viable VBA approach for this. I've tried different coding approaches (Application.EnableEvents, CalculateManyTimes, etc) with no luck. Surely this kind of question has been asked before but I could not find it. Any tips would be appreciated. Thank you.
基于我的网络搜索，似乎一个循环和启用的结合是一个可行的VBA方法。我尝试过不同的编码方法(应用程序)。使能事件，计算任何时间，等等)没有运气。这类问题以前肯定有人问过，但我找不到。如有任何建议，我们将不胜感激。谢谢你！
1 个解决方案
#1
1
The key is Application.CalculateFull so the code could be:
关键是应用程序。充分计算，使代码可以:
Sub CalculateAndSave()
Dim Ws As Worksheet
Set Ws = Worksheets(2)
For i = 1 To 100
Application.CalculateFull
Ws.Range(Ws.Cells(4, i), Ws.Cells(23, i)) = Sheets(1).Range("BC4:BC23").Value
Next i
End Sub
#1
1
The key is Application.CalculateFull so the code could be:
关键是应用程序。充分计算，使代码可以:
Sub CalculateAndSave()
Dim Ws As Worksheet
Set Ws = Worksheets(2)
For i = 1 To 100
Application.CalculateFull
Ws.Range(Ws.Cells(4, i), Ws.Cells(23, i)) = Sheets(1).Range("BC4:BC23").Value
Next i
End Sub