Excel VBA显示For-Next循环中每次迭代的结果 [英] Excel VBA to show results of each iteration in For-Next loop

问题描述

Sub TrialOne()

    Dim loop_ctr As Integer
    For loop_ctr = 1 To 100
        Sheets("Capacity&Costs").Activate
        Range("Vans") = "=INT(RAND()*3+1)-1"   'Generate random number
        Range("c26:c29") = 0    'Account for large van's lead time
        Range("d29") = 0    'Account for small van's lead time
        Range("VanCapacity").Formula = "=Sum(g4:h4)"    'Calculate total van capacity
        Range("LostBusiness").Formula = "=k4-l4"    'Calculate lost business
        Range("VanPurchase").Formula = "=Sum(o4:p4)" 'Calculate Van Purchase
        Range("RunningCost").Formula = "=Sum(S4:t4)"  'Calculate Running Cost
        Range("LostBusinessCost").Formula = "=M4*B44"    'Calculate Lost Business Cost
        Range("Cost").Formula = "=sum(q30,u30,w30)" 'Calculate Cost
        Calculate

    Next loop_ctr

End Sub

我有上面的代码我的最终输出是Cost，如图所示。我想重复这个操作100次，所以我得到100个不同的值为成本。现在这个代码重复了100次的动作，但是我得到的结果是成本的第100个值。我想获得100个不同价值的成本的列表，其列表如下所示：

Hi, I have the above code. My final output is "Cost" as indicated. I want to repeat this operation 100 times, so I get 100 different values for "Cost". Right now this code repeats the action for 100 times but all I get at the end is the 100th value for "Cost." I would like to obtain a list of the 100 different values of "Cost", a list that looks something like below:

Trial 1 -       $1000
Trial 2 -       $2000
Trial 3 -       $3000

范围第四行中的Vans由0，1或2的整数组成。如果有其他信息，我应该提供，请让我知道。

Range"Vans" in the fourth line is made of integers that are either 0, 1, or 2. If there is any other information I should provide, please let me know.

推荐答案

在计算成本之后，一个简单的方法可以在您的循环中，将该值复制到一个新的单元格，您可以在每个迭代中向下移动一个。

An easy way to do it would be within your loop, after you have calculated the cost, copy that value to a new cell, which you shift down by one in each iteration.

例如

    Range("Cost").Formula = "=sum(q30,u30,w30)" 'Calculate Cost
    Calculate

    'write the cost to a cell
    Range("A" & loop_ctr).Value = "Trial " & loop_ctr
    Range("B" & loop_ctr).Value = Range("Cost").Value

Next loop_ctr

这篇关于Excel VBA显示For-Next循环中每次迭代的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！