Solver macro not working at all

xrb08162

New Member
Joined
Jul 19, 2010
Messages
8
Hi, I am writing a macro to automatically set up and run the solver add in for excel (I am using excel 2000). When I run the macro however, nothing happens whatsoever to the values in the cells. When I run the solver manually however, it works fine. Can anyone tell me why my macro doesn't work?

This is my macro:

Sub solver_test1()
Worksheets("Calculations").Activate
SolverReset
SolverOk SetCell:="$C$30", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$20"
SolverAdd CellRef:="$F$7", Relation:=2, FormulaText:="$L$7"
SolverAdd CellRef:="$F$7", Relation:=3, FormulaText:=0
SolverAdd CellRef:="$C$27", Relation:=2, FormulaText:=60000
SolverSolve
End Sub


Incidentally, when I open the solver window to run it manually, the three constraints set up in the macro are already entered (ie. F7=L7, F7 >=0, and C27=60000) and the type of solve (ie max, min or equal to value) is already selected. However, the cell references for which cell is the target cell (i.e. C30) and which cell is to be changed (ie. C20) are blank. Putting the command UserFinish:=True/False after SolverSolve has no effect.

Before you ask, I have activated the solver add-in in my excel workbook and referenced it in my VB editor...

Any ideas would be much appreciated.

Thank you excel gurus!

Douglas
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
I vaguely recall a problem with some old version of Excel (and I no longer have 2000 on any machine to test) that required I respecify the target and 'by changing' cells. See if duplicating the SolverOk statement just before the SolverSolve helps.
 
Upvote 0
Hmm... Thanks, but I'm afraid that didn't work. I put the respecify line in just before the solversolve line but it hasn't worked. Any more ideas?
 
Upvote 0
If I record the macro by doing it manually, it gives me exactly the same code as before:

SolverOk SetCell:="$C$30", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$20"
SolverSolve

And this works the first time, but then after that doesn't work again...

I'm very confused
 
Upvote 0
Ah, brilliant! I'm afraid I'm in the same position as you but I have got onto the IT department to see what they can do, but it may take an age. I've found myself a non-networked PC with Excel 2003 and it works fine on that so I'll carry on there.

Thanks for your help
 
Upvote 0
I just called up the IT department and they told me that somebody called with exactly the same problem and gave the same web address. I'm pretty sure we work in the same company! If so, pretty freaky that we posted our issues in about five minutes of each other. Hope we aren't working on the same task too.
 
Upvote 0
Haha, that's fantastic! I'm sure we do work in the same company too. Hopefully since we have both reported the problem they'll fix it quickly. My name is Douglas Herbert so from that I'm sure you'll work out my company email address (revealing nothing online)...
 
Upvote 0
Another thing I recall might be of relevance. Searching Google led me to a page written by Jon Peltier. He and I (and probably some others) had discussed this issue and while he wrote it up I did not. See if it helps to load the solver add-in if it is not and to then initialize it with
Code:
Application.Run "Solver.xla!Solver.Solver2.Auto_open"

Jon's entire post is at http://peltiertech.com/Excel/SolverVBA.html#Solver3
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top