VBA help for minimizing a solution using multiple scenarios

[bugarin1010]

Hi,

First, thanks to everyone for reading my post. Here's my problem:

I have nearly two thousands variable cells with the equation randbetween(0,3). Based on the values in these cells, my solution changes. I want to run 250-or so scenarios to obtain the minimum value from these scenarios. Also, I want to be able to copy the solution result as well as the value of the variable cells so that I can re-create these results.

I'm currently on "manual update" so I want to use "F9" to re-calculate the solution cell since it will change the "randbetween" variable cells.

Here's the worksheet information:

Worksheet name: Results
Solution cell: B4
Variable cells: B7:B2000

I want to copy the solution cell starting in cell D4 and proceeding to E4, F4 all the way to IV4

At the same time, I want to copy the variable cells into E7:E2000 and proceeding to F7:F2000 all the way to IV7:IV2000.

Here's the VBA code that works great, except that it isn't moving to the next column to paste the results! Can someone help me correct it?

Sub Automation()

Dim i As Integer
Set Results = Sheets("Results")

Results.Activate

For i = 4 To 253
    
    Calculate
    Range("B4").Select
    Selection.Copy
    
    Range("D4") = Cells(4, i)
    Range("D4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("B7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Range("D7") = Cells(7, i)
    Range.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Next i

End Sub

Thanks!

 

[pgc01]

Hi bugarin1010

Try:

Sub Automation()
Dim i As Integer
 
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
 
With Worksheets("Results")
    For i = 4 To 253
        
        Calculate
        
        .Cells(4, i).Value = .Range("B4").Value
    
        .Range(.Cells(7, i), .Cells(20000, i)).Value = .Range("B7:B20000").Value
        
    Next i
End With
 
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[bugarin1010]

Hi PGC,

Thanks so much! Works exactly as I wanted it to. Thanks again!

Best,
Cesar