VBA to Automate Scenarios

[bkanne]

Hi,

I need a macro that can change the value of a specific cell to all of the values in the rows beneath it, stopping when it reaches a blank cell.

Each time the cell is changed, the spreadsheet needs to calculate (equivalent of pressing F9) before moving onto the next row.

In my example, cell "D9" (which has a hardcoded value of 1) would need to change to each of the values in rows beneath it (D10:D59, each of which has the numbers 1-50 in them) and at each point calculate the spreadsheet. Ideally, the code would stop at whatever the max number in the rows beneath it is / stop when it reaches a blank cell.

Thank you so much!

 

[JLGWhiz]

Hi,

I need a macro that can change the value of a specific cell to all of the values in the rows beneath it, stopping when it reaches a blank cell.

Each time the cell is changed, the spreadsheet needs to calculate (equivalent of pressing F9) before moving onto the next row.

In my example, cell "D9" (which has a hardcoded value of 1) would need to change to each of the values in rows beneath it (D10:D59, each of which has the numbers 1-50 in them) and at each point calculate the spreadsheet. Ideally, the code would stop at whatever the max number in the rows beneath it is / stop when it reaches a blank cell.

Thank you so much!

Maybe this

Sub t(0
Dim c As Range
   With ActiveSheet
       Application.Calculation = xlCalculationManual
           For Each c In .Range("D10", .Cells(Rows.Count, 4).End(xlUp))
                 Range("D9").value = c.Value
                .Calculate
          Next
          Application.Calculation =xlCalculationAutomatic
    End With
End Sub

 

[bkanne]

Thanks so much for the reply! This works perfectly for running through the scenarios, but it doesn't calculate after each number is selected. I also need it to stay in Manual mode as well..

The equivalent of what I'm trying to do without code is: Change cell D9 to 2 - press F9, change to 3 - Press F9, change to 4 - Press F9, etc.

Any thoughts on how to make it calc at each step instead of just at the end?

 

[JLGWhiz]

Thanks so much for the reply! This works perfectly for running through the scenarios, but it doesn't calculate after each number is selected. I also need it to stay in Manual mode as well..

The equivalent of what I'm trying to do without code is: Change cell D9 to 2 - press F9, change to 3 - Press F9, change to 4 - Press F9, etc.

Any thoughts on how to make it calc at each step instead of just at the end?

If you step through the code, you will see that it does calculate on every iteration of the loop. But you will not be able to see the results, except for the last iteration because of the speed which it loops. If you want to see the results of each iteration then put a message box in after the .Calculate line and it will stop there each time so you can observe the results. But if you do that, you might just as well continue doing the process manually.

Or maybe put a pause in the code like

Sub t(0
Dim c As Range
   With ActiveSheet
       Application.Calculation = xlCalculationManual
           For Each c In .Range("D10", .Cells(Rows.Count, 4).End(xlUp))
                 Range("D9").value = c.Value
                .Calculate
                s = Timer + .5
                Do While Timer < s
                    DoEvents
                Loop
          Next
          Application.Calculation =xlCalculationAutomatic
    End With
End Sub

 

[JLGWhiz]

You can change the length of the delay by changing the 0.5 to 1, 1.5, 2 or whatever timespan you want in seconds. Also, just delete the line that resets the calculation to automatic if you want it to remain in manual mode.

 

[bkanne]

Thanks for the help JLG Wiz.

I ended up using the following code:

Sub Run_Scenarios_2()    
d_max = Sheets("Scenario Analysis").Cells(Rows.Count, 4).End(xlUp).Row
    If d_max < 10 Then Exit Sub
    Application.Wait Now + TimeSerial(0, 0, 1)
    For d = 1 To d_max - 9
        Sheets("Scenario Analysis").Range("D9") = d
        Calculate
        Application.Wait Now + TimeSerial(0, 0, 1)
    Next d
    Sheets("Scenario Analysis").Range("D9") = 1
        Calculate
            MsgBox "Scenario Analysis Complete"
End Sub

This calculated each scenario without having to stop each time.

Thanks so much for all your help though!

 

[JLGWhiz]

Thanks for the help JLG Wiz.

I ended up using the following code:

Sub Run_Scenarios_2()    
d_max = Sheets("Scenario Analysis").Cells(Rows.Count, 4).End(xlUp).Row
    If d_max < 10 Then Exit Sub
    Application.Wait Now + TimeSerial(0, 0, 1)
    For d = 1 To d_max - 9
        Sheets("Scenario Analysis").Range("D9") = d
        Calculate
        Application.Wait Now + TimeSerial(0, 0, 1)
    Next d
    Sheets("Scenario Analysis").Range("D9") = 1
        Calculate
            MsgBox "Scenario Analysis Complete"
End Sub

This calculated each scenario without having to stop each time.

Thanks so much for all your help though!

Thanks for the feedback,
regards, JLG