Macro Loop for Solver

**kchm_2000** · 12-27-2010, 09:54 AM

Hi,

I am very new to macro and am trying to create a loop for solver, but I can't seem to get it right

basically I have 2 separate sets of solver

1st: minimize col L by changing col M to S & also make sure sum of col M to S = 1

each set of solver only pertains to 1 row e.g. minimize cell L2 by changing M2:S2 & Sum(M2:S2)=1

2nd: minimize col V by changing col W to AC

each set of solver only pertains to 1 row e.g. minimize cell V2 by changing W2:AC2

I would appreciate the help given by anyone thanks

**shg** · 12-27-2010, 12:25 PM

Welcome to the forum.

Try this:

Private Sub CommandButton1_Click()
    Dim i           As Long
    Dim cell        As Range

    For Each cell In Range("L2", Cells(Rows.Count, "L").End(xlUp))
        Application.StatusBar = "Solving " & cell.Address(False, False)
        SolverReset
        SolverOk SetCell:=cell.Address, _
                 MaxMinVal:=2, _
                 ByChange:=Rows(cell.Row).Range("M1:S1").Address
        SolverAdd CellRef:=Rows(cell.Row).Range("T1").Address, _
                  Relation:=2, _
                  FormulaText:="1"
        SolverSolve UserFinish:=True
    Next cell

    For Each cell In Range("V2", Cells(Rows.Count, "V").End(xlUp))
        Application.StatusBar = "Solving " & cell.Address(False, False)
        SolverReset
        SolverOk SetCell:=cell.Address, _
                 MaxMinVal:=2, _
                 ByChange:=Rows(cell.Row).Range("W1:AC1").Address
        SolverSolve UserFinish:=True
    Next cell
    
    Application.StatusBar = False
End Sub

**kchm_2000** · 12-27-2010, 12:58 PM

thanks for your help, is there a possibility of changing it so that there is no need for a command button, sorry for the trouble and the duplicate post.

**shg** · 12-27-2010, 01:14 PM

You can remove the Private part and change the name of the sub to anything you like, then run it from the macro dialog (Alt+F8).

**kchm_2000** · 12-27-2010, 01:25 PM

i tried adding another module but but it gives me

compile error:

sub or function no defined

on the solver reset line

the error file is attached to this comment

**Alf** · 12-27-2010, 01:32 PM

You need to set a reference to Solver in Visual Basic:

To reference the Microsoft Excel Solver add-in for macros in your workbook, use the following steps:
Open your workbook.
On the Tools menu, point to Macro, and then click Visual Basic Editor.
On the Tools menu, click References.
In the Available References list, click to select the Solver.xls check box, and then click OK.

Note If you do not see Solver.xls in the Available References list, click Browse. In the Add Reference dialog box, locate and select the Solver.xla file, and then click Open. The Solver.xla file is typically found in the C:\Program Files\Microsoft Office\Office\Library\Solver subfolder.
You are now ready to use the Microsoft Excel Solver functions in a VBA macro.

**kchm_2000** · 12-28-2010, 06:54 AM

Great it works, you can't believe how grateful I am. But I need to add one more constrain to my solver can you please help me add for both

1st: minimize col L by changing col M to S & also make sure sum of col M to S = 1

each set of solver only pertains to 1 row e.g. minimize cell L2 by changing M2:S2 & Sum(M2:S2)=1

AND ALSO make sure that cells M2:S2 are all positive

2nd: minimize col V by changing col W to AC

each set of solver only pertains to 1 row e.g. minimize cell V2 by changing W2:AC2

AND ALSO make sure that cells W2:AC2 are all positive

I would like to know also how to adjust the macro if I have to add more variables i.e. incease number of asset factors

thanks a lot

**aftab.vg** · 12-28-2010, 07:02 AM

Hi,
I just want to know that if I have a data in a excel sheet (Heading like BRANCH NAME & PRODUCT NAME) branch name like Agra, Delhi, Noida, Patna, Pune etc. I just want to write a macro in excel that when I run the macro automatically make a branch wise separate sheet like Agra’s related data in a separate sheet Delhi’s related separate sheet etc.
Please help ([email protected])

**Alf** · 12-28-2010, 09:42 AM

Hi aftab.vg

Don't break forum rules.

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

kchm_2000

Your request is way over my present macro/solver know how so hopefully shg will fix your problems.

Alf

Macro Loop for Solver

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro Loop for Solver

re: Macro Loop for Solver

Re: Macro Loop for Solver

Re: Macro Loop for Solver

Re: Macro Loop for Solver

Re: Macro Loop for Solver

Re: Macro Loop for Solver

Re: Macro Loop for Solver

Re: Macro Loop for Solver

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions