Solver button working on one sheet but not another.

Hey all, hoping you can help.

I am creating a workbook with multiple different sheets for solving for variables. I created a button on sheet 2 using the record function and it works great. I tried doing the same thing for sheet 1 but it gives "run-time error '1004'" Application-defined or object-defined error"

I'm unsure what causes this. I will also point out that I can't even manually use solver on these sheet via the Data tab.

Any help is appreciated.

P.S. - Solver is enabled in my VBA and added to my worksheet, the file is .xlsm

Originally Posted by Alf

Could it be that you are trying to run solver on a non active sheet? Because solver is build to run only on the active sheet.



Alf

The function is supposed to solve using the sheet it is on. Could it be because the cells it calls are the same regardless of the sheet? It is in the VBA as $Q$16 or something for both functions since one sheet is similar to the other so I copied and built it up.

I think you should upload the file as I'm not sure what function you are referring to

The function is supposed to solve using the sheet it is on

as I think of solver as an "Add-in" and not as a function.

Alf

Originally Posted by Alf

I think you should upload the file as I'm not sure what function you are referring to

as I think of solver as an "Add-in" and not as a function.

Alf

I've added it under manage attachments but I cannot seem to see it attached here.

Here is the code however:

Sub SolverSoldier()
'
' SolverSoldier Macro
'

'
SolverOk SetCell:="$Q$16", MaxMinVal:=3, ValueOf:=1, ByChange:="$L$16", Engine _
:=1, EngineDesc:="GRG Nonlinear"
SolverSolve userFinish:=True
End Sub


and then I created a function called SolverSheet which is the exact same but won't work on sheet 1.

Solver can only be run on the active sheet so you could make your function to a macro that sets sheet1 as the active sheet and the run solver. Alternative loop through Sheet1 and Sheet2

Please Login or Register  to view this content.

This macro will run solver on Sheet1 and Sheet2. It also assumes that the setting of target cell, cell to change and target value are the same on both sheets. Calling the solver engine it is sufficient to just name the engine i.e. Engine:=1 instead of Engine:=1, EngineDesc:=GRG Nonlinear. Excel is rather fuzzy when building macros. Adding the line "SolverReset" stops buildup of constraint when running solver in a loop. In this case really not needed.

Alf