+ Reply to Thread
Results 1 to 8 of 8

VBA Solver Macro not working

  1. #1
    Registered User
    Join Date
    07-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2010
    Posts
    22

    VBA Solver Macro not working

    Hi.
    I have adapted a VBA macro I looked up in microsoft excel support site, but it doesn´t seem to work exactly how I wanted to.
    This macro is using the Solver to calculate a value for each row of information I have.
    I have set up an example of 3 rows where i want it to calculate the values in cells F2:F4. But it seems to be calculating only for cell F2
    I have an attached file with the example
    Can anyone help?
    Attached Files Attached Files

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: VBA Solver Macro not working

    There's no code in your file.

    Row\Col
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    1
    PVP Antes Qtd Antes Novo PVP QTD Simulada E1 Vendas Atuais Vendas Simuladas Delta Vendas E2
    2
    2
    20
    3
    18
    -0.20
    40.00 €
    54.00 €
    26%
    -0.2
    3
    2
    20
    4
    14
    -0.30
    40.00 €
    56.00 €
    29%
    -0.3
    4
    2
    20
    5
    11
    -0.30
    40.00 €
    55.00 €
    27%
    -0.3
    5
    6
    0.000
    K6: =SQRT(SUMXMY2(G2:G4, K2:K4))
    7
    8
    Set K6 to Min by changing F2:F4
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    07-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: VBA Solver Macro not working

    Wrong file...sorry.
    I have attached a new one. Hope it's ok now.
    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: VBA Solver Macro not working

    Why don't you try what I posted first? Your Solver model was completely hosed.

  5. #5
    Registered User
    Join Date
    07-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: VBA Solver Macro not working

    I don't really know if I understood your solution.
    I have set K6 to what you proposed, but I don't know what to do next.
    What I want is the values in F2:F4 to be set automatically
    Last edited by Bruno Silva; 11-29-2014 at 03:28 PM.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: VBA Solver Macro not working

    Data > Solver,

    Set Objective: K6

    To: Min

    By Changing Variable Cells: F2:F4

    Subject to the Constraints (none; delete all those you have)

    Then press Solve; does that give the desired results?


    It's useful to know how to use Solver from the user interface before trying to automate it, no?

  7. #7
    Registered User
    Join Date
    07-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: VBA Solver Macro not working

    Thank you very much.
    This gives me the desired results.

    I really do appreciate the time you took to help me!

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: VBA Solver Macro not working

    Good job, you're welcome.

+ 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: 0
    Last Post: 07-20-2014, 12:45 PM
  2. Need Input to working Solver Macro
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-17-2012, 05:28 PM
  3. solver macro + simulation code + not updating solver values
    By sabinemaria in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2012, 11:37 AM
  4. Solver addin not working in Macro
    By JK1234 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-11-2010, 02:12 PM
  5. Solver - VB macro not working
    By rcaus in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-26-2009, 02:47 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