Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Solver macro not working at all

  1. #1
    New Member
    Join Date
    Jul 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Solver macro not working at all

    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

  2. #2
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Solver macro not working at all

    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.

  3. #3
    New Member
    Join Date
    Jul 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Solver macro not working at all

    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?

  4. #4
    New Member
    Join Date
    Jul 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Solver macro not working at all

    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

  5. #5
    New Member
    Join Date
    May 2006
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Solver macro not working at all

    This seems to refer to the problem you and I have: http://support.microsoft.com/kb/821430

    I am unable to download any 'hotfixes' just now because I'm work in a company that needs approval for that. Can you please try it out and let me know how it goes? My post is: http://www.mrexcel.com/forum/showthread.php?t=483075

  6. #6
    New Member
    Join Date
    Jul 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Solver macro not working at all

    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

  7. #7
    New Member
    Join Date
    May 2006
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Re: Solver macro not working at all

    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.

  8. #8
    New Member
    Join Date
    Jul 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Solver macro not working at all

    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)...

  9. #9
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Solver macro not working at all

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •