Rolling Excel Solver

joe888 · Aug 28th 2011

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

junho lee · Aug 28th 2011

Re: Rolling Excel Solver

Hi joe888,
try this, code from web.

Code

Sub Macro2()
'check solver reference in Tools
    Dim SetCell As Range, ByChangingCell As Range, ValueCell As Range
    Dim rSetCell As Range, rUp As Range, rLow As Range
    Set rSetCell = Range("B16:H16")
    For Each SetCell In rSetCell
        With SetCell
            .GoalSeek Goal:=0, ChangingCell:=.Offset(-1)
        End With
    Next
    Set rSetCell = Range("B18:H18")
    For Each SetCell In rSetCell
        With SetCell
            .GoalSeek Goal:=0, ChangingCell:=.Offset(-1)
        End With
    Next
    Set rSetCell = Range("B13:H13")
    For Each SetCell In rSetCell
        Set ByChangingCell = SetCell.Offset(-3)
        Set rUp = SetCell.Offset(1)
        Set rLow = SetCell.Offset(6)
        SolverReset
        SolverAdd CellRef:=ByChangingCell.Address(True, True), Relation:=1, FormulaText:=rUp.Address(True, True)
        SolverAdd CellRef:=ByChangingCell.Address(True, True), Relation:=3, FormulaText:=rLow.Address(True, True)
        SolverOk SetCell:=SetCell.Address(True, True), _
                 ByChange:=ByChangingCell.Address(True, True), _
                 MaxMinVal:=3, ValueOf:=0, Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve userFinish:=True
        SolverFinish KeepFinal:=1
    Next
End Sub

Display More

regards, junho

joe888 · Aug 29th 2011

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

junho lee · Aug 29th 2011

Re: Rolling Excel Solver

Hi joe,
In the VBE, Tools > References, make sure that Solver it ticked.
Regards, junho

joe888 · Aug 29th 2011

Re: Rolling Excel Solver

Hi Junho,

definetely I tick the solver before running macros (vba->tools-> reference-> solver. I m using excel 2007.

junho lee · Aug 29th 2011

Re: Rolling Excel Solver

sorry for late.
can you delete : [ , Engine:=1, EngineDesc:="GRG Nonlinear" ]
regards, junho

joe888 · Aug 29th 2011

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

junho lee · Aug 29th 2011

Re: Rolling Excel Solver

Hi joe,
Yes your correct.
Regards, junho

joe888 · Aug 29th 2011

Re: Rolling Excel Solver

Thanks Junho

sincerely
Joe

junho lee · Aug 29th 2011

Re: Rolling Excel Solver

Hi joe,
Change

Code

Range("B18:[COLOR=#ff0000][B]H18[/B][/COLOR]")

to

Code

Range("B18", Range("B18").End(xlToRight))

regards, junho

Rolling Excel Solver

Participate now!

Share