0

have created this macro that allows the user to select scenario numbers using an InputBox and now I'm trying to run the scenarios and record some sort of results. This is very useful for cases that I might want to run +50 scenarios at the time. Scenario numbers trigger OFFSETs in my input sheet where it analyzes results. This works perfectly in break mode but when I actually run the macro as a "user" it just works for the first scenario and not the rest

Sub sbScenarioAnalysis()
Sheets("results").Select
Range("J24").Select
Do While ActiveCell.Value <> Empty

    Range("ScenarioNumber").Value = ActiveCell.Value

      Application.Calculate
        If Not Application.CalculationState = xlDone Then
        ActiveCell.Offset(1, 0) = Range("TL")
        ActiveCell.Offset(2, 0) = Range("ML")
        End If
    ActiveCell.Offset(0, 1).Select
Loop
End Sub
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
VLookUp
  • 1
  • 3
  • 3
    I'd suggest reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4088852). – Comintern Feb 06 '19 at 20:22
  • Not sure if this completely fixes your problem or not but selecting sheets cells is unreliable and very bad practice. You can still use the same idea but better is to create row and col variables then set `row` to 24 and `col` to 10. Then increment `col` each time through the loop and access via `Cells(row, col)`. – Mark Balhoff Feb 06 '19 at 20:22
  • I've read your code, and except for `ActiveCell.Offset(1, 0) = Range("TL")` and `ActiveCell.Offset(2, 0) = Range("ML")` it doesn't seem to be doing anything. So I assume you rather wanted to say `If Application.CalculationState = xlDone Then` ... VBA is single-threaded, hence after you call `Application.Calculate`, the `CalculationState` should always be `xlDone` and your code inside the `If` block would never been called. By the way, I think you should really follow @Comintern suggestion, this code relying on what the `Active` / `Selected` ranges are is very unstable. – Matteo NNZ Feb 06 '19 at 21:07

0 Answers0