Programming with Solver

**brandonp** · 10-09-2006, 03:21 PM

Any help on this would be GREATLY appreciated. I am trying to have a solver program solve problems based on certain constraints placed on the cells. This is the code that has been generated:

-----------------------------------------------------------------------
Sub Format_Solver_DRP()
' Format_Solver_DRP Macro
' Macro recorded 09/14/2006 by Lynn Holt
Dim row_num As Double
'Set row_num to starting row in worksheet
row_num = 2
SOLVER.Auto_open
Do
'SOLVER.SolverReset
'Run solver for each row independently
'Check to see if row/cell in B column has a value or not
If Range("B" & row_num).Value = "" Then
Else
Worksheets("Sheet2").Activate
'Clear solver parameters
'Set parameters
SolverOk SetCell:=Range("P" & row_num).Address, MaxMinVal:=2, ValueOf:="0", ByChange:=Range("F" & row_num, "G" & row_num).Address
'Set constraints

SolverChange CellRef:=Range("F" & row_num).Value, Relation:=4, FormulaText:="integer"
SolverChange CellRef:=Range("G" & row_num).Value, Relation:=4, FormulaText:="integer"
SolverChange CellRef:=Range("M" & row_num).Value, Relation:=2, FormulaText:=Range("N" & row_num).Value
SolverChange CellRef:=Range("G" & row_num).Value, Relation:=3, FormulaText:="0"
SolverChange CellRef:=Range("F" & row_num).Value, Relation:=3, FormulaText:="0"
SolverOk SetCell:=Range("P" & row_num).Value, MaxMinVal:=2, ValueOf:="0", ByChange:=Range("F" & row_num, "G" & row_num).Address
'SOLVER.SolverSave
'Run solver
SolverSolve UserFinish:=True
'Keep results
'SOLVER.SolverFinish KeepFinal:=1
row_num = row_num + 1
End If
'Increase row number by 1

'Break loop if row/cell is null or blank
Loop Until Range("B" & row_num).Value = ""
Dim exit_loop As Boolean

-----------------------------------------------------------------------

The problem is, the first row (f2 and g2) will run correctly, but the following rows don't seem to follow the same constraints. The other odd thing, is if you start the macro at the f3 and g3, it will work for that row, but not the rows that follow. Is there something wrong with the code that isn't allowing the constraints to follow the cells? Please help!

**brandonp** · 10-10-2006, 06:33 AM

bump.....................

**Myles** · 10-10-2006, 10:14 AM

Check the formula in the Target cell Range("P" & row_num).Address. If unresolved post file.

**brandonp** · 10-10-2006, 12:37 PM

I am not sure what im looking for in that expression that could be wrong? Please advise. Thank you for replying!

**brandonp** · 10-10-2006, 12:43 PM

attaching code as a txt.

**Myles** · 10-10-2006, 01:40 PM

Try this edited version. You had SOLVERCHANGE instead of SOLVERADD in setting the constraints. I have recast the ranges using Cells Method for clarity and also supplied a range to copy solver results to. Make sure rolling Cells(i,"P") contain formulas linking your constrained ranges.

Sub Format_Solver_DRP()
Dim i As Long
SolverReset
'Set i to starting row in worksheet
i = 2
'SOLVER.Auto_open
Do
If Cells(i, "B").Value = "" Then
Else
Worksheets("Sheet1").Select
'Set parameters
SolverOk SetCell:=Cells(i, "P"), MaxMinVal:=2, ValueOf:="0", ByChange:=Range(Cells(i, "F"), Cells(i, "G"))
'Set constraints

SolverAdd Cells(i, "F").Value, 4, "integer"
'SolverAdd Range(i, "F").Value, 3, "0" '===> this "duplicates" the constraints on Cells(i,"F"); are the 2 compatible?
SolverAdd Cells(i, "G").Value, 4, "integer"
'SolverAdd Cells(i, "G").Value, 3, "0" '===> again, this "duplicates" the constraints on Cells(i,"G"); are the 2 compatible?
SolverAdd Cells(i, "M").Value, 2, Cells(i, "N").Value

SolverSolve UserFinish:=True

'Collect results in columns X and Y
Range(Cells(i, "X"), Cells(i, "Y")).Value = Range(Cells(i, "F"), Cells(i, "G")).Value
'SOLVER.SolverFinish KeepFinal:=1
i = i + 1
End If
Loop Until IsEmpty(Cells(i, "B"))

End Sub

**brandonp** · 10-10-2006, 02:03 PM

Attached is the code we used by using your baseline. We didn't get an output, so I dont know if it worked or not. Could you take a look at our modifications?

Thanks again for helping!

**Myles** · 10-10-2006, 02:31 PM

Try attached code in txt.file. If you're still having problems send your spreadsheet.

**brandonp** · 10-10-2006, 02:51 PM

It is not outputing anything. It is adding the constraints though now for each row, but I think to get the correct results, wouldnt we only want the constraints to be listed in the constraints box for the row we are dealing with?

How can I get the excel file to you? The forums wont let me upload an excel file.

**Myles** · 10-10-2006, 03:02 PM

Not knowing the struture of your layout and the interconnectivity between the various sets of ranges, I have trumped up a wild scenario to link the target cell to the Changing Range. The code outputs without any hitch.

See my excel demo file attached, another way to debunk your suggestion that the forum forbids transmission of excel files. Make sure the file size is less than 100k. Use zip to reduce size.

**brandonp** · 10-10-2006, 03:08 PM

Thank you, I gave it to my boss for reference. Here is our spreadsheet as you have requested.

**Myles** · 10-10-2006, 04:07 PM

Code is running and delivering outputs. However, you may have to have a critical look at the reasonableness of any constraints you may wish to impose. I for instance, discovered that, given the current structure of your table, Solver has difficulty setting the values in Column G to "Integer". Some sort of overall rationalisation is needed based on the nature of data you are analyzing. see file attached

**brandonp** · 10-10-2006, 04:16 PM

might that be why we are getting negative values? Based on the constraints, we shouldnt be getting any negative values....

My boss is looking at the code now. We are getting ready to leave for the day so I will respond in the morning. Thanks again for all of your help it is greatly appreciated!

**brandonp** · 10-10-2006, 04:19 PM

Also, i just noticed this, but production quantity and shipped quantity should be the same. I dont know if we have the constraint coded for that though.

**brandonp** · 10-10-2006, 04:21 PM

Also, you see the blue results box that says the first ilne should be 26 and 29? That is correct, but the lines below it are not. That has been our problem, the first line always runs correctly, but the following lines do not return the right values.

**brandonp** · 10-10-2006, 04:23 PM

F + G should = M which should = O

this is solved by getting the best combination of F and G to get P to equal as close to 0 as possible.

**brandonp** · 10-11-2006, 07:17 AM

ready to work on this again!

**brandonp** · 10-11-2006, 07:22 AM

What version of excel are you running? The spreadsheet you gave me will not compile?

**Myles** · 10-11-2006, 10:16 AM

F + G should = M which should = O

What is the formula in Column M and Column N? From the spreadsheet you sent Column N adds up Columns F & G. How does that stand against F + G should = M? Something of a mixup....

Again, constraining the values in Columns F & G to be integers doesn't seem right. Do you mean to constrain their values to be GREATER THAN or EQUAL to zero? That sounds more meaningful. Get the basics right and everything will fall into place. It's not a difficult optimization case.

**brandonp** · 10-11-2006, 10:19 AM

Sorry about that, I meant F + G should = N which should equal M

F and G should be greater than or equal to zero, as everything should.

**Myles** · 10-11-2006, 10:33 AM

Do you also know that column N contains a formula that adds up Columns F &G? Surely, you wouldn't need to have Columns M and N containing the same formula.

**brandonp** · 10-11-2006, 10:45 AM

You are right, it would be pointless to have two columns with the same formula. If you look, column M is static. It's values are typed in by the user. Column N is populated as a check.

M is titled production quantity, which is how many pieces are produced. F and G are basically the columns showing what needs to be shipped based on what site they are coming from. Column N adds those two together.

**Myles** · 10-11-2006, 11:38 AM

See file attached for a solution to your problem. Post back any further problems, if any.

**brandonp** · 10-11-2006, 12:12 PM

Can you send us your solver.xla? No data comes up when we hit the button. The results look good though.

**Myles** · 10-11-2006, 12:22 PM

File attached again. See code as requested.

Sub FormatSolver()

Dim i As Long
SolverReset

i = 2
Do

Worksheets("Sheet2").Select
'Set parameters
SolverOk SetCell:=Cells(i, "P"), MaxMinVal:=2, ValueOf:="0", ByChange:=Range(Cells(i, "F"), Cells(i, "G"))
'constraints
SolverAdd Cells(i, "F"), 3, "0.0000000001"
SolverAdd Cells(i, "G"), 3, "0.0000000001"
SolverAdd Cells(i, "N"), 2, Cells(i, "M")

SolverSolve UserFinish:=True
'Collect results in columns Q and R
Range(Cells(i, "Q"), Cells(i, "R")).Value = Range(Cells(i, "F"), Cells(i, "G")).Value
'clear constraints
SolverDelete Cells(i, "F"), 3, "0.0000000001"
SolverDelete Cells(i, "G"), 3, "0.0000000001"
SolverDelete Cells(i, "N"), 2, Cells(i, "M")

i = i + 1
Loop Until IsEmpty(Cells(i, "B"))
SolverReset
End Sub

**brandonp** · 10-11-2006, 12:32 PM

Still no dice. If we take out what is in cells f and g to run it, no results are displayed when we hit the button. Can you send us your solver.dll and solver.xla?

**Myles** · 10-11-2006, 12:45 PM

find attached.

**Myles** · 10-11-2006, 12:48 PM

sorry,

file size of solver addin + dll exceed max limit for transmission even after zipping.

**Myles** · 10-11-2006, 12:59 PM

One more thing. Have you checked the reference to SOLVER in the VBEditor? As you well know, it needs to be set for the macro to run Solver.

**brandonp** · 10-12-2006, 07:00 AM

Yes, we have. Not sure what the problem is then...

**brandonp** · 10-16-2006, 10:10 AM

What version of Excel are you using?

Programming with Solver

LinkBack

Thread Tools

Rate This Thread

Display

Programming with Solver

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions