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 https://db.tt/2eaD8n4v — Mierzen, May 24 '14 at 20:08
@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, May 24 '14 at 22:01
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, May 24 '14 at 22:06
@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, May 24 '14 at 23:06
I only had one workbook open. Maybe it's the engine reference that causes it somehow. Will try tomorrow morning and keep you posted — Mierzen, May 24 '14 at 23:08
I've removed the `Engine:=1`'s and then running the macro gave an error saying that the Solver add-in couldn't be found. I moved the sheet selection to the start of the macro, then it runs fine, but solver never actually changes any values. Moving the button to the data sheet also does not help - the macro also runs without changing any values — Mierzen, May 25 '14 at 06:58

score 0 · Accepted Answer · answered May 25 '14 at 07:20

0

Solved!

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

answered May 25 '14 at 07:20

Mierzen

566
1
5
25

Sheets().Select/Solver VBA not working

1 Answers1