+ Reply to Thread
Results 1 to 5 of 5

VBA Solver - Stuck at "Setting up Problem"

  1. #1
    Registered User
    Join Date
    02-16-2017
    Location
    Norway
    MS-Off Ver
    Office365
    Posts
    3

    VBA Solver - Stuck at "Setting up Problem"

    Hi!

    Im trying to make a solver function work but it is now stuck at "Setting up problem" anything i can do with this? All cells have been named in my excel sheet so im assuming i can put those names into the code instead of e"C48". The math behind the solver is not that advanced either, so it doesnt make sence to me why it wont go further then setting up problem.
    I also added the excel sheet itself.
    Any help would be appreciated
    Code:

    Sub SolverFB()


    Worksheets("Data Sheet").Activate

    SolverOk SetCell:=Range("Freeboard"), MaxMinVal:=3, ValueOf:=1.5, ByChange:=Range("Ballast_weight,Length")

    SolverAdd CellRef:=Range("Length"), Relation:=3, FormulaText:=("Length_min")
    SolverAdd CellRef:=Range("Length"), Relation:=1, FormulaText:=("Length_max")

    SolverAdd CellRef:=Range("GM"), Relation:=3, FormulaText:=Range("GM_min")
    SolverAdd CellRef:=Range("GM"), Relation:=1, FormulaText:=Range("GM_max")

    SolverAdd CellRef:=Range("Freeboard"), Relation:=3, FormulaText:=Range("FB_min")
    SolverAdd CellRef:=Range("Freeboard"), Relation:=1, FormulaText:=Range("FB_max")

    SolverSolve userFinish:=True


    End Sub
    Attached Files Attached Files

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: VBA Solver - Stuck at "Setting up Problem"

    Looking at the values in your uploaded file there is some strange values.

    Cell B30, the number is formatted as text as well B35 and B52. The numbers in B31 and B32 is given as 1.8 and 0.05 where I would have expected 1,8 and 0,05

    Opening up Solver see uploaded jpg you do have a number of constraints i.e. "Freeboard <= 1,5" and "Freeboard >= 1,5" so in this macro the line
    Please Login or Register  to view this content.
    is missing and I'm not sure I understand the logic behind "Freeboard <= 1,5" and "Freeboard >= 1,5"

    solv_problem.jpg

    The macro "Sub Solver4" could do with a bit of trimming, not wrong the way it's written but perhaps easier to read i.e.

    Please Login or Register  to view this content.
    Alf
    Last edited by Alf; 03-27-2017 at 10:02 AM.

  3. #3
    Registered User
    Join Date
    02-16-2017
    Location
    Norway
    MS-Off Ver
    Office365
    Posts
    3

    Re: VBA Solver - Stuck at "Setting up Problem"

    "Freeboard <= 1,5" and "Freeboard >= 1,5" came in cause i forgot to remove the constraint for freeboard. Thanks for checking it out, ill try and see what i get!

  4. #4
    Registered User
    Join Date
    02-16-2017
    Location
    Norway
    MS-Off Ver
    Office365
    Posts
    3

    Re: VBA Solver - Stuck at "Setting up Problem"

    Not sure how my cells got formated as Text but i changed those and it fixed it. Thanks for the help!

  5. #5
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: VBA Solver - Stuck at "Setting up Problem"

    You are welcome and thanks for feedback

    Alf

    Ps As this have solved your problem don't forget to mark thread "Solved"

    To mark your thread solved do the following:
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 03-14-2017, 02:08 AM
  2. MonthView1.Format....("YYYY/MM/DD") Problem setting up accurate value...2007
    By Excelnoub in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-20-2014, 10:30 AM
  3. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  4. Replies: 11
    Last Post: 10-30-2013, 02:11 PM
  5. Problem using the "solver function".
    By MathiasH in forum Excel General
    Replies: 7
    Last Post: 11-17-2011, 03:49 PM
  6. Solver VBA - Problem - How to rid of Dialog Box "Single Step"
    By hahnchen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2008, 01:40 AM
  7. Setting values for "TRUE","FALSE" and "#REF!"
    By Hurtige in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-11-2006, 07:31 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1