Sheets().Select/Solver VBA not working

Question

My workbook has three sheets, one of which's names is "Data".

Why on earth does the .select method not work? The code runs fine, without giving errors, but never selects/goes to the "Data" sheet...

Private Sub CommandButton1_Click()

Dim solverAvailable As Boolean
solverAvailable = CheckSolver()

Sheets("Data").Select
If solverAvailable = True Then

    SolverReset
    SolverOk SetCell:="$D$56", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$12:$C$13", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$H$56", MaxMinVal:=2, ValueOf:=0, ByChange:="$D$12:$D$13", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$M$56", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$12:$E$13", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$F$77", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$14:$C$15", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$L$77", MaxMinVal:=2, ValueOf:=0, ByChange:="$D$14:$D$15", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$S$77", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$14:$E$15", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$D$133", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$83:$C$84", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$H$133", MaxMinVal:=2, ValueOf:=0, ByChange:="$D$83:$D$84", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$M$133", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$83:$E$84", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$F$157", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$85:$C$86", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$L$157", MaxMinVal:=2, ValueOf:=0, ByChange:="$D$85:$D$86", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$S$157", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$85:$E$86", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$D$209", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$163:$C$164" _
        , Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$H$209", MaxMinVal:=2, ValueOf:=0, ByChange:="$D$163:$D$164" _
        , Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$M$209", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$163:$E$164" _
        , Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$F$231", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$165:$C$166" _
        , Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$L$231", MaxMinVal:=2, ValueOf:=0, ByChange:="$D$165:$D$166" _
        , Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True

    SolverReset
    SolverOk SetCell:="$S$231", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$165:$E$166" _
        , Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True
End If

Beep
End Sub

I would actually like the sheet selection to be inside the if, but if it is in there, it gives a "9" error: subscript out of range!

Ideally, I don't even want to select the sheet - I would like to reference Sheets("Data").Range(whatever), but that also gives the subscript out of range error.

I've tried that and it made no difference. Here is the original file, if you want to take a look db.tt/2eaD8n4v — Mierzen
@Mierzen In Excel 2007, after removing the references to Engine in the various Solver statements, it runs fine and selects the Data sheet. The fact that you are getting subscript out of range errors usually means that VBA is not finding the "Data" worksheet. What happens if you try to select it, or reference a range, from the Immediate Window, using the same syntax? — Ron Rosenfeld
I don't know what the engine reference does, I got that from the macro recorder. Unfortunately, I don't have my pc with me atm, so I'm not able to check now. I don't know why it can't find that sheet, it certainly is there. Sheets(3) also gives the subscript error — Mierzen
@Mierzen The engine reference is something that was added in 2010. It has to do with the method Solver uses, and shouldn't affect things. However, with your workbook running on my machine, there is no problem with selecting either sheets("data") or Sheets(3). See what happens in the immediate window when you have your pc. Also, check to make certain the correct workbook is "ThisWorkbook" at the time you are running your macro. If a different workbook is active, you may need to reference the workbook by name. — Ron Rosenfeld

Mierzen Mierzen · Accepted Answer · 2014-05-25T07:20:44

Solved!

I removed the CheckSolver() function and call (I got it from http://peltiertech.com/Excel/SolverVBA.html), now it works perfectly!

Sheets().Select/Solver VBA not working

1 Answers