CODEWITHSUNDEEP
excelvba
blah_crusader
blah_crusader

Reputation: 434

VBA: Runtime error 1004 using Solver

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

Upvotes: 4

Views: 10616

Answers (2)

Will Ediger
Will Ediger

Reputation: 893

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.

Upvotes: 2

Ram Narasimhan
Ram Narasimhan

Reputation: 22506

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.

Upvotes: 2

Related Questions