+ Reply to Thread
Page 1 of 3 1 2 3 LastLast
Results 1 to 15 of 33

Macro with Solver loop

  1. #1
    Registered User
    Join Date
    12-15-2016
    Location
    Seattle, WA
    MS-Off Ver
    Microsof Office Pro Plus 2013
    Posts
    10

    Macro with Solver loop

    Hi -

    I recorded my step for Solver as a Macro (code below) and am trying to have the macro replicate the steps for all cells in column AJ (there will be 60K+ cells in the workbook). I have no real VBA skills to speak and I need help please! Thanks!

    Sub Macro1()
    '
    ' Macro1 Macro
    '

    '
    SolverOk SetCell:="$AJ$2", MaxMinVal:=2, ValueOf:=5, ByChange:="$AJ$2", Engine _
    :=1, EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$AJ$2", MaxMinVal:=2, ValueOf:=5, ByChange:="$AJ$2", Engine _
    :=1, EngineDesc:="GRG Nonlinear"
    SolverSolve
    End Sub

  2. #2
    Registered User
    Join Date
    12-15-2016
    Location
    Seattle, WA
    MS-Off Ver
    Microsof Office Pro Plus 2013
    Posts
    10

    Re: Macro with Solver loop

    For reference, these are my Solver specs:
    Capture.PNG

  3. #3
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,169

    Re: Macro with Solver loop

    Perhaps a macro like this then?

    Please Login or Register  to view this content.
    As you have constraints in your solver setup but not in the recorded macro I'm a bit unsure if they should be included or not. In this macro these constraints are included.

    In the future don't forget to wrap you code when posting.

    Alf

  4. #4
    Registered User
    Join Date
    12-15-2016
    Location
    Seattle, WA
    MS-Off Ver
    Microsof Office Pro Plus 2013
    Posts
    10

    Re: Macro with Solver loop

    Hi Alf - I copied the macro code into the VBA editor and ran the macro but nothing in the data changed. It's like it didn't run.

  5. #5
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,169

    Re: Macro with Solver loop

    Strange. Could you click on solver and see if setup is changed i.e

    target cell should be $AJ$xxx where xxx is the last row number with data in the AJ column. All the other parameters should be change accordingly

    "AJxxx < = "AExxx" and so forth. This should indicate that the solver loop is working.

    As for the data change could you upload a small sample file say a 100 rows so that I could do some testing.

    Alf

  6. #6
    Registered User
    Join Date
    12-15-2016
    Location
    Seattle, WA
    MS-Off Ver
    Microsof Office Pro Plus 2013
    Posts
    10

    Re: Macro with Solver loop

    Hi Alf - Even with 25 rows the file is too large. Can we take this thread offline over email so that I can send you the file?

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,793

    Re: Macro with Solver loop

    If I may interject, the indicated Solver model does not make sense to me. Your Solver model has both your objective function (the function to minimize in Set Target Cell) and your decision variables (the input to change in the By Changing cells) as AJ2. I would expect the Set target and the by changing to be different cells. Check your spreadsheet and your Solver model to make sure that you have defined these elements of the Solver model correctly.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  8. #8
    Registered User
    Join Date
    12-15-2016
    Location
    Seattle, WA
    MS-Off Ver
    Microsof Office Pro Plus 2013
    Posts
    10

    Re: Macro with Solver loop

    @mrshorty - makes sense. I've edited the Changing cell to AK2 which gives me back the same answer in AJ2 so I think that will work. I've edited the macro Alf wrote but it still is not running. I click run and nothing happens.

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,793

    Re: Macro with Solver loop

    If AK2 is simply =AJ2 (or AJ2 is =AK2), I'm not sure you have really changed anything.

    So that I understand, which way did you change it -- set AJ2 to a minimum by changing AK2 subject to the constraint that AK2 is between 0 and AE2, or did you go with set AK2 to a minimum by changing AJ2 subject to the constraint that AJ2 is between 0 and AE2, or some other variation?

    In almost all of those cases -- assuming Solver doesn't error -- here's how I would solve the problem as currently specified

    1) The smallest value for AJ2 (=AK2) that is between 0 and AE2 is 0, so set AJ2=0 (or set AK2 to 0, which is mirrored by AJ2).
    2) If AP2 (some function of AJ2, I would guess???) is less than 5, adjust AJ2 (AK2) until AP2=5.

    So, the solution will always be either AJ2=0 or the value of AJ2 that cause AP2 to be 5. If AP2 is always greater than 5, then AJ2 will always be 0, and it will look like Solver didn't run, because once AJ2 is 0, there will be nothing to change.

    I think we need to better understand what function is in column AJ, how it relates to AP, and try to understand exactly what the objective function and decision variables should be.

  10. #10
    Registered User
    Join Date
    12-15-2016
    Location
    Seattle, WA
    MS-Off Ver
    Microsof Office Pro Plus 2013
    Posts
    10

    Re: Macro with Solver loop

    Gotcha, yeah it might help if i explain what I'm trying to achieve. I'll lay it out by what cells are what.

    AE2: current price (this is a number value cell)
    AI2: profit per unit at current price (this is a number value cell)
    AJ2: promotional price (this is a formula cell)
    AK2: promotional profit per unit (=AI2-(AE2-AJ2) (formula cell)
    AP2: absolute value of promotion price/promotional profit per unit (=abs(AJ2/AK2)) (formula cell)

    I'm trying to find what the minimum AJ2 would be that would give me an AP2 that is greater than 5 (5 at a minimum). AJ2 has to be greater than 0 (since it's a selling price) and has to be less than AE2 since it's a promotional price.

  11. #11
    Registered User
    Join Date
    12-15-2016
    Location
    Seattle, WA
    MS-Off Ver
    Microsof Office Pro Plus 2013
    Posts
    10

    Re: Macro with Solver loop

    to clarify for AJ2 = there is no formula in the cell currently. this is what I'm trying to solve for.

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,793

    Re: Macro with Solver loop

    It looks like AP2 might be a better objective function/Set target cell. Some math/algebra before we get to programming:

    1) AP2=ABS(AJ2/AK2)=ABS(AJ2/(AI2-AE2+AJ2)). You have not given any indication what is in AI2, but I would guess that it is some function of AE2.
    2) At one extreme: AJ2=0, AP2 becomes ABS(0/AK2)=0
    3) At the other extreme: AJ2=AE2, AP2 becomes ABS(AJ2/(AI2-AE2+AJ2))=ABS(AE2/AI2), which, I assume will always be greater than 5.

    So, somewhere between AJ2=0 and AJ2=AE2, AP2 is equal to 5. Are you certain that there is only one possible value for AJ2 where AP2=5, or are there multiple possible values for AJ2 where AP2=5?

    Here's how I would analyze the problem next:

    4) Set AP2=5 and solve for AJ2 --- 5=ABS(AJ2/(AI2-AE2+AJ2)) This will probably involve two runs through, one where the denominator is positive and one where the denominator is negative (if the denominator can be both positive and negative, given the other constraints). If AI2 does not involve AJ2, then this should be fairly straightforward algebra, you will get one or two possible solutions for AJ2, choose the smaller. In this scenario, Solver is not even needed, since we found the solutions algebraically. You would only need to enter the appropriate formula into AJ2 at AP2=5.
    4a) To be absolutely certain that this results in the desired solution, you may want to check what happens at AJ2 slightly larger than 0 or slightly less than AE2, and at other values for AJ2 to make sure that there are no surprises in the behavior of AP2.

    I realize that this is all math and no programming so far, but this may yield easier programming solutions, once the math is understood.

  13. #13
    Registered User
    Join Date
    12-15-2016
    Location
    Seattle, WA
    MS-Off Ver
    Microsof Office Pro Plus 2013
    Posts
    10

    Re: Macro with Solver loop

    So I'm not necessarily trying to find the value that gives me AP2=5. I'd like to the find the minimum value of AJ2 that gets me as close as possible to AP2=5, therefore AP2 >= 5 in the constraints in Solver. AI2 is technically a function of AE2 but these are both specific data points that I have and are not formulas or variables that will change. AI2 will adjust at the same rate that AE2 does (i.e. if AE2 increased by 5, AI2 increases by 5). I don't believe it can be done in the manner you're saying because no there will not be just one possible value as I'm trying to find the minimum value and as AI2 and AK2 could be negative values.

  14. #14
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,793

    Re: Macro with Solver loop

    Without more information, I will probably have to take your word for it that this cannot be solved algebraically. If AI2 is independent of AJ2, though, it really looks to me like the algebra should work out for a closed form solution of the equation/inequality. I think if I were solving this problem, I would pursue solving the above equation/inequality for AJ2. From that, I would learn how this function behaves and have a better idea how to solve it. If it ends up requiring a numeric Solver solution, I would expect to end up with an expression for AJ2 as a function of AP2. Then call Solver and tell it to set AJ2 to a minimum by changing AP2 subject to the constraint that AP2>=5 and any other constraints needed when the problem is rearranged in that way.

  15. #15
    Registered User
    Join Date
    12-15-2016
    Location
    Seattle, WA
    MS-Off Ver
    Microsof Office Pro Plus 2013
    Posts
    10

    Re: Macro with Solver loop

    I've ran solver manually on the first 20 cells and it is giving me the answer I need. I just need to be able to create a loop to do this calculation on every cell.

+ Reply to Thread
Page 1 of 3 1 2 3 LastLast

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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