Rolling Excel Solver

  • Hi everyone,



    I have 7 columns to estimate variables using 2 goal seek and one solver function. I can do them manually just every time by opening goal seek and solver putting parameters of each column and do it 7 times. I need macros automatically can estimate these variables using 2 goal seek functions and 1 solver function. Excel sheet is located at : http://www.box.net/shared/icf50cbdhxy2yhprizin
    Hence I have these 2 goal seek functions for each 7 column:
    first Goal seek for 7 columns respectively:
    For column 2
    Set cell: B16
    To value: 0
    By changing cell: B15
    For column 3
    Set cell: C16
    To value: 0
    By changing cell: C15

    For column 4
    Set cell: D16
    To value: 0
    By changing cell: D15

    For column 5
    Set cell: E16
    To value: 0
    By changing cell: E15
    For column 6
    Set cell: F16
    To value: 0
    By changing cell: F15
    For column 7
    Set cell: G16
    To value: 0
    By changing cell: G15
    For column 8
    Set cell: H16
    To value: 0
    By changing cell: H15

    Second Goal seek for 7 columns respectively:
    For column 2
    Set cell: B18
    To value: 0
    By changing cell: B17
    For column 3
    Set cell: C18
    To value: 0
    By changing cell: C17

    For column 4
    Set cell: D18
    To value: 0
    By changing cell: D17

    For column 5
    Set cell: E18
    To value: 0
    By changing cell: E17
    For column 6
    Set cell: F18
    To value: 0
    By changing cell: F17
    For column 7
    Set cell: G18
    To value: 0
    By changing cell: G17
    For column 8
    Set cell: H18
    To value: 0
    By changing cell: H17

    Solver function for 7 columns respectively
    For column 2
    Set target cell: B13
    Equal to value of : 0
    By changing cells: B10
    Subject to constraints: B10 <= B14
    B10 >= B19

    For column 3
    Set target cell: C13
    Equal to value of : 0
    By changing cells: C10
    Subject to constraints: C10 <= C14
    C10 >= C19

    For column 4
    Set target cell: D13
    Equal to value of : 0
    By changing cells: D10
    Subject to constraints: D10 <= D14
    D10 >= D19
    For column 5

    Set target cell: E13
    Equal to value of : 0
    By changing cells: E10
    Subject to constraints: E10 <= E14
    E10 >= E19
    For column 6

    Set target cell: F13
    Equal to value of : 0
    By changing cells: F10
    Subject to constraints: F10 <= F14
    F10 >= F19
    For column 7

    Set target cell: G13
    Equal to value of : 0
    By changing cells: G10
    Subject to constraints: G10 <= G14
    G10 >= G19
    For column 8

    Set target cell: H13
    Equal to value of : 0
    By changing cells: H10
    Subject to constraints: H10 <= H14
    H10 >= H19



    After this calculation i will get Omega, Omega1 and Omega2 for each column. So I need a macros which calculates these solver function automatically for each column.

    Thanks in advance

  • Re: Rolling Excel Solver


    Hi joe888,
    try this, code from web.


    regards, junho

  • Re: Rolling Excel Solver


    Juhno Thank you very much for your quick reply. Macros runs but it gives "compile error: named argument not found" named argument is engine:=


    I will highly appreciate if you provide me help on that issue


    Sincerely
    Joe

  • Re: Rolling Excel Solver


    Junho It is unbelievable...!!! It works and gives the the same result with manual estimation..


    Thank you very much...I just appreciate your advanced skills..


    Just last question...now I m going to implement this macros (works up to 7 column) to my real data which contains not 7 columns (end at H) but 662 columns(ends at YM)
    And to do so on as I understand I need to change the following:


    Changing: Range("B16:H16") to Set rSetCell = Range("B16:YM16")

    Range("B18:H18") to Range("B18:YM18")


    Range("B18:H13") to Range("B18:YM13")


    I will highly appreciate if you check my post?
    Sincerely


    Joe

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!