optimum - questions for experts

M

Mark

Hi,

I looking for smart solve (in VBA) the following
hypothetical example:

How count optimum R?

W = POINTS[(2NAME)*INDEX(BEETWEEN 100 AND 120)]
K = POINTS[(3NAME*INDEX(BEETWEEN 110 AND 170)]

R = W + K

Necessary conditium: Counted Points must have total INDEX
550
Click to expand...

NAME INDEX POINTS
AAA 100 35
AAA 140 31
AAA 120 30
AAA 120 35
AAA 100 32
AAA 120 31
AAA 100 34
AAA 140 30
BBB 105 31
BBB 122 33
BBB 105 33
BBB 122 38
BBB 105 29
BBB 142 32
etc....


Please advise I would be very grateful.
Best regards
Mark
 
K

keepitcool

The Solver Addin can help you here...

Make sure the solver addin is loaded

The in the VBE add a REFERENCE to solver.xla

Then try following code.. I've asuumed that you want minimum points,
while index >=550.

The column D on sheet2 will give you your optimum combination.


Sub Optimize()
Dim src As Range, crit As Range, dest As Range
Dim rPP As Range, rPI As Range, rCH As Range

'Filter out the valid rows
With Worksheets(1)
Set src = .Range(.[c1], .[a65536].End(xlUp))
End With

Set crit = Worksheets(2).[a1:c1]
crit.Parent.Cells.Clear
crit.Offset(0) = src.Rows(1).Value
crit.Offset(1) = Array("AAA", ">=100", "<=140")
crit.Offset(2) = Array("BBB", ">=110", "<=170")


src.AdvancedFilter xlFilterCopy, crit.Resize(3), crit.Offset(5)
Set dest = crit.Offset(5).CurrentRegion
Set dest = dest.Offset(1).Resize(dest.Rows.Count - 1, 4)
dest.Columns(4) = 0

Set rPP = Cells(1, 4)
Set rPI = Cells(2, 4)
Set rCH = dest.Columns(4)

rPP.Formula = "=sumproduct(" & dest.Columns(2).Address & "," &
rCH.Address & ")"
rPI.Formula = "=sumproduct(" & dest.Columns(3).Address & "," &
rCH.Address & ")"

crit.Parent.Activate
'THIS NEEDS a VB REFERENCE to SOLVER.XLA

SolvReset
SolvOk rPI.Address, 2, 0, rCH.Address
SolvAdd rPP.Address, 3, "=550"
SolvAdd rCH.Address, 4, "integer"
SolvAdd rCH.Address, 1, "=1"
SolvOptions MaxTime:=10, AssumeLinear:=True, AssumeNonNeg:=True
SolvSolve UserFinish:=True
End Sub




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


Mark said:
Hi,

I looking for smart solve (in VBA) the following
hypothetical example:

How count optimum R?

W = POINTS[(2NAME)*INDEX(BEETWEEN 100 AND 120)]
K = POINTS[(3NAME*INDEX(BEETWEEN 110 AND 170)]

R = W + K

Necessary conditium: Counted Points must have total INDEX
550
Click to expand...

NAME INDEX POINTS
AAA 100 35
AAA 140 31
AAA 120 30
AAA 120 35
AAA 100 32
AAA 120 31
AAA 100 34
AAA 140 30
BBB 105 31
BBB 122 33
BBB 105 33
BBB 122 38
BBB 105 29
BBB 142 32
etc....


Please advise I would be very grateful.
Best regards
Mark
Click to expand...
 
B

Bernie Deitrick

Mark,

You haven't adequately described your problem or calculation
technique. Give an example using actual numbers.

HTH,
Bernie


Mark said:
Hi,

I looking for smart solve (in VBA) the following
hypothetical example:

How count optimum R?

W = POINTS[(2NAME)*INDEX(BEETWEEN 100 AND 120)]
K = POINTS[(3NAME*INDEX(BEETWEEN 110 AND 170)]

R = W + K

Necessary conditium: Counted Points must have total INDEX
550
Click to expand...

NAME INDEX POINTS
AAA 100 35
AAA 140 31
AAA 120 30
AAA 120 35
AAA 100 32
AAA 120 31
AAA 100 34
AAA 140 30
BBB 105 31
BBB 122 33
BBB 105 33
BBB 122 38
BBB 105 29
BBB 142 32
etc....


Please advise I would be very grateful.
Best regards
Mark
Click to expand...
 
K

keepitcool

Mark,

oops.. you'll need to move 1 line a little upwards:

crit.Parent.Activate '<<should go to before the set rPP=

Set rPP = Cells(1, 4)
Set rPI = Cells(2, 4)
Set rCH = dest.Columns(4)

suc6

keepITcool
 
M

Mark

more exact formula:

W = POINTS[(2NAME) with INDEX(BEETWEEN 100 AND 120)]
K = POINTS[(3NAME with INDEX(BEETWEEN 110 AND 170)]

R = W + K
Necessary conditium:
Counted Points must have total INDEX => 550

How count optimum R?
Kindly regards
Mark



-----Original Message-----
Hi,

I looking for smart solve (in VBA) the following
hypothetical example:

How count optimum R?

W = POINTS[(2NAME)*INDEX(BEETWEEN 100 AND 120)]
K = POINTS[(3NAME*INDEX(BEETWEEN 110 AND 170)]

R = W + K

Necessary conditium: Counted Points must have total INDEX
550
Click to expand...

NAME INDEX POINTS
AAA 100 35
AAA 140 31
AAA 120 30
AAA 120 35
AAA 100 32
AAA 120 31
AAA 100 34
AAA 140 30
BBB 105 31
BBB 122 33
BBB 105 33
BBB 122 38
BBB 105 29
BBB 142 32
etc....


Please advise I would be very grateful.
Best regards
Mark

.
Click to expand...
 
M

Mark

Hi,
More detail, please - i don't know solver with VBA very
well.
besides...
Pop-up error 1004 in below line:
Set dest = dest.Offset(1).Resize(dest.Rows.Count - 1, 4)

How can I assign VBE REFERENCE to SOLVER.XLA?

Many thanks in anticipation!
Mark


-----Original Message-----
The Solver Addin can help you here...

Make sure the solver addin is loaded

The in the VBE add a REFERENCE to solver.xla

Then try following code.. I've asuumed that you want minimum points,
while index >=550.

The column D on sheet2 will give you your optimum combination.


Sub Optimize()
Dim src As Range, crit As Range, dest As Range
Dim rPP As Range, rPI As Range, rCH As Range

'Filter out the valid rows
With Worksheets(1)
Set src = .Range(.[c1], .[a65536].End(xlUp))
End With

Set crit = Worksheets(2).[a1:c1]
crit.Parent.Cells.Clear
crit.Offset(0) = src.Rows(1).Value
crit.Offset(1) = Array("AAA", ">=100", "<=140")
crit.Offset(2) = Array("BBB", ">=110", "<=170")


src.AdvancedFilter xlFilterCopy, crit.Resize(3), crit.Offset(5)
Set dest = crit.Offset(5).CurrentRegion
Set dest = dest.Offset(1).Resize(dest.Rows.Count - 1, 4)
dest.Columns(4) = 0

Set rPP = Cells(1, 4)
Set rPI = Cells(2, 4)
Set rCH = dest.Columns(4)

rPP.Formula = "=sumproduct(" & dest.Columns(2).Address & "," &
rCH.Address & ")"
rPI.Formula = "=sumproduct(" & dest.Columns(3).Address & "," &
rCH.Address & ")"

crit.Parent.Activate
'THIS NEEDS a VB REFERENCE to SOLVER.XLA

SolvReset
SolvOk rPI.Address, 2, 0, rCH.Address
SolvAdd rPP.Address, 3, "=550"
SolvAdd rCH.Address, 4, "integer"
SolvAdd rCH.Address, 1, "=1"
SolvOptions MaxTime:=10, AssumeLinear:=True, AssumeNonNeg:=True
SolvSolve UserFinish:=True
End Sub




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


Mark said:
Hi,

I looking for smart solve (in VBA) the following
hypothetical example:

How count optimum R?

W = POINTS[(2NAME)*INDEX(BEETWEEN 100 AND 120)]
K = POINTS[(3NAME*INDEX(BEETWEEN 110 AND 170)]

R = W + K

Necessary conditium: Counted Points must have total INDEX
550
Click to expand...

NAME INDEX POINTS
AAA 100 35
AAA 140 31
AAA 120 30
AAA 120 35
AAA 100 32
AAA 120 31
AAA 100 34
AAA 140 30
BBB 105 31
BBB 122 33
BBB 105 33
BBB 122 38
BBB 105 29
BBB 142 32
etc....


Please advise I would be very grateful.
Best regards
Mark
Click to expand...

.
Click to expand...
 
K

keepitcool

in EXCEL: tools/addins CHECK solver.
in VBE: tools/references, CHECK solver.xla

see my earlier OOPS re the runtime error
the line to activevate the dest sheet must come before the
assignment of the RPP/RPI variables.



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
M

Mark

Hi,
I do it finally! :) but..
Probably i inexactly describe my example. Sorry
Data in column Index is kilometers and Solver should count
only max points with fulfil total INDEX min 550(km).
In example are 2 kind of name (AAA, BBB) but really is
more name >200.

Solver is OK - that's news for me.
Is it do only in VBA also.

Sincere Thanks
Mark
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combining MATCH & LOOKUP formulas 7
INDEX MATCH formula 8
Return Matched Numeric Labels across Single Row 4
Use only VBA - request for experts 2
Are CRT monitor registers standard? 3
find largest data (part2) hard work.. 1
14 Day Average REVISITED 3
Nested Do...Loops and Date Cell Defined as Date in VBA code 4

Top