none
Excel 2010 and Solver Issue

    Question

  • I have a spreadsheet that uses the solver addin. It works fine in 2003, but when I run it in 2010 I get the following error:

    Run-time error '1004':
    Application-defined or object-defined error

    The SolverSolve line in the code below is genereating the error.

        SolverOk SetCell:="$AE$4", MaxMinVal:=2, ValueOf:="0", ByChange:="$AE$6:$AE$10"
        SolverOptions MaxTime:=1000, Iterations:=1000, Precision:=0.00000001, _
            AssumeLinear:=False, StepThru:=False, Estimates:=1, Derivatives:=2, _
            SearchOption:=1, IntTolerance:=5, Scaling:=True, Convergence:=0.0000000001, _
            AssumeNonNeg:=False
        SolverOk SetCell:="$AE$4", MaxMinVal:=2, ValueOf:="0", ByChange:="$AE$6:$AE$10"
        SolverSolve

    Any ideas on how to fix this. I have verified that Solver is installed correctly.

    Thanks

    Wednesday, January 5, 2011 5:41 PM

Answers

  • Hi

    Try changing your cell references to Range(cell ref)

    eg. SetCell:="$AE$4",  to SetCell:=Range("$AE$4")

    You haven't included a SolverReset in your example, so if you haven't got one it might be a good idea to add one first.

    Hope this helps

     


    G North MMI
    • Proposed as answer by G North Thursday, January 6, 2011 5:35 PM
    • Marked as answer by mtbmikey Thursday, January 6, 2011 6:03 PM
    Thursday, January 6, 2011 4:48 PM

All replies

  • Did you check your references?  Make sure that the reference for SOLVER is selected in VBE.  Since you've migrated to 2010, that might not be the case anymore.

     

    HTH,

     

    Eric

    Wednesday, January 5, 2011 6:35 PM
  • Check that reference!  I believe it is 14.0 in Office 2010:

    http://msdn.microsoft.com/en-us/library/ff629397.aspx

    I'm still using 2007, and it is definitely 12.0 here.

    Thursday, January 6, 2011 1:47 AM
  • Thanks! I'll give that a try in the morning.
    Thursday, January 6, 2011 4:32 AM
  • I checked in the references. It had two selected, "Missing SOLVER.XLA" and "Solver". Unchecked the missing one and still get the same error.

    The weird thing is, when I step through it, the preceding SolverOK and SolverOptions calls step through fine. When it gets to SolverSolve, it fails.

    Thursday, January 6, 2011 4:20 PM
  • Hi

    Try changing your cell references to Range(cell ref)

    eg. SetCell:="$AE$4",  to SetCell:=Range("$AE$4")

    You haven't included a SolverReset in your example, so if you haven't got one it might be a good idea to add one first.

    Hope this helps

     


    G North MMI
    • Proposed as answer by G North Thursday, January 6, 2011 5:35 PM
    • Marked as answer by mtbmikey Thursday, January 6, 2011 6:03 PM
    Thursday, January 6, 2011 4:48 PM
  • The SolverReset addition fixed it. Thanks!
    Thursday, January 6, 2011 5:19 PM