VBA: Runtime error 1004 using Solver

Question

I'm new to VBA, and have some trouble with using the Solver in a macro. I'm assigning a macro to a command button in Excel, and they calculate everything perfectly, but an ugly error shows up as well at the end ("Runtime error 1004, method calculation of object _application failed").

Sub serieus1()
'
' serieus1 Macro
'  

'
    SolverReset
    SolverAdd CellRef:="$R$15", Relation:=2, FormulaText:="1"
    SolverAdd CellRef:="$L$18", Relation:=2, FormulaText:="$B$3"
    SolverOk SetCell:="$L$19", MaxMinVal:=2, ValueOf:=0, ByChange:="$L$15:$Q$15", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve

End Sub

Try changing SetCell:="$L$19" to SetCell:=Range("$L$19"). Also, try adding SolverFinish after SolverSolve — Automate This
– Automate This, Commented Feb 23, 2014 at 16:58

L42 · Accepted Answer · 2014-11-05 23:57:27Z

2

I was getting the same error in my Solver macro.

I tried some of the things mentioned in several Excel forums. As the comment suggests, I did add SolverFinish, and also wrapped all my SetCells to of Range type. I also saved the file and exited out of Excel.

That did the trick and the pop-up error vanished for me when I got back in.

edited Nov 5, 2014 at 23:57

L42

19.8k11 gold badges47 silver badges70 bronze badges

answered Nov 5, 2014 at 23:46

Ram Narasimhan

22.6k5 gold badges54 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Will Ediger · Accepted Answer · 2015-02-20 19:18:23Z

2

I was getting this runtime error using Solver through the GUI, after I had been running some SolverOk's in VBA. I came across a post on MSDN, "Excel 2010 and Solver Issue," and it recommended adding a SolverReset before you run your SolverOk. I ran SolverReset in the Immediate Window, and that got rid of the runtime error.

Previously I didn't have SolverFinish as Ram Narasimhan recommended, and if I had, that might have prevented the error in the first place, but that's just conjecture.

answered Feb 20, 2015 at 19:18

Will Ediger

90310 silver badges17 bronze badges

1 Comment

FlorianH Over a year ago

SolverReset solved my problem, while SolverFinish had not helped/sufficed.

Collectives™ on Stack Overflow

VBA: Runtime error 1004 using Solver

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related