Excel VBA to show results of each iteration in For

问题:

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

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

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.

回答1:

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.

e.g.

    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