Hi all,

I am in desperate need of help regarding a problem with combinationa
optimization. I have tried many ways but i just cannot get the require
results. May i seek your help in this?

The excel VBA code below is supposed to generate all relevan
combinations (and ONLY the relevant combinations) that satisfy th
equation Qsys = Q1 + Q2 + Q3 + Q4 + Q5. Q1 to Q5 have maximum an
minimum values. Qsys is a fixed constant that is determined befor
running code.
However, the code fails to do as required. I believe it's a logica
problem but i just cannot figure out.


Because my actual code has up to Q10, i need to generate ONLY relevan
combinations that satisfy equation, so that the runtime will not tak
too long.

Many thanks for your kind attention and i await your reply i
anticipation!

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


Sub PumpFlowCombinationVer1()

' This Macro generates all possible combinations of flow distribution
' between pumps to meet required System Flow.
' Divisions of 0.1m(^3)are used.

' Please feed in the necessary parameters before running the Macro.

' Good Day!


'''''''''''''''''''''''' Declaration
'''''''''''''''''''''''''''''''''''''''''''

Dim x As Double

Dim Qsummax As Double

Dim Qsys, Q1, Q2, Q3, Q4, Q5 As Double

' Upper boundary conditions for flows
Dim Q1max, Q2max, Q3max, Q4max, Q5max As Double

' Lower boundary conditions for flows
Dim Q1min, Q2min, Q3min, Q4min, Q5min As Double

' Counters
Dim b, c, d, e, f As Double

' Temporary variables
Dim Qtemp As Double


'''''''''''''''''''''''' Initializatio
'''''''''''''''''''''''''''''''''''''''''''



' Assigning System Flow
Qsys = Round(Range("Interface!B6"), 1)

''''''''''''''''''''''''''''''''''''''''''''''''' TO BE REMOVED
TESTING ONLY

Qsys = 0.6

' Assigning Boundary Flow Values
Q1max = Range("Input!B10")
Q2max = Range("Input!C10")
Q3max = Range("Input!D10")
Q4max = Range("Input!E10")
Q5max = Range("Input!F10")


Q1min = Range("Input!B11")
Q2min = Range("Input!C11")
Q3min = Range("Input!D11")
Q4min = Range("Input!E11")
Q5min = Range("Input!F11")


' Assigning Counter Initial Values (To be placed after Qmi
initialization)
b = Q1min
c = Q2min
d = Q3min
e = Q4min
f = Q5min



x = 4

'''''''''''''''''''''''' Main Bod
''''''''''''''''''''''''''''''''''''''''''''''''


Qsummax = Q1max + Q2max + Q3max + Q4max + Q5max

If (Qsys Qsummax) Then

MsgBox "System Flow requirements exceed total pump capacity. Please ru
all pumps at full speed. Insufficient System Flow is being provided a
present."


Else

Do While Application.Round(b, 1) <= Q1max

If (Qsys - b - c - d - e - f = 0) Then

Do While Application.Round(c, 1) <= Q2max

If (Qsys - b - c - d - e - f = 0) Then

Do While Application.Round(d, 1) <= Q3max

If (Qsys - b - c - d - e - f = 0) Then

Do While Application.Round(e, 1) <= Q4max

If (Qsys - b - c - d - e - f = 0) Then

Do While Application.Round(f, 1) <= Q5max

If (Qsys - b - c - d - e - f = 0) And (b + c + d + e + f = Qsys) Then

Cells(x, 1) = b
Cells(x, 2) = c
Cells(x, 3) = d
Cells(x, 4) = e
Cells(x, 5) = f

Cells(x, 7) = b + c + d + e + f
Debug.Print x, b, c, d, e, f, b + c + d + e + f



x = x + 1

Else
End If

f = f + 0.1
Loop
f = Q5min

Else
End If

e = e + 0.1
Loop
e = Q4min

Else
End If

d = d + 0.1
Loop
d = Q3min

Else
End If

c = c + 0.1
Loop
c = Q2min

Else
End If

b = b + 0.1
Loop
b = Q1min

End If

End Su

--
highjumpe
-----------------------------------------------------------------------
highjumper's Profile: http://www.excelforum.com/member.php...fo&userid=2651
View this thread: http://www.excelforum.com/showthread.php?threadid=39866

anyone? :(


--
highjumper
------------------------------------------------------------------------
highjumper's Profile: http://www.excelforum.com/member.php...o&userid=26516
View this thread: http://www.excelforum.com/showthread...hreadid=398664

Your attempt to terminate loops early eliminates the opportunity to find
legitimate answers.

This appears to get all the answers and is faster than the unconstrained
version:

I didn't change them, but if you want 3 variables to be doubles, you can't
use

Dim a, b, c as double

you must use

Dim as as Double, b as Double, c as Double

in the original, a and b are variants.

Sub PumpFlowCombinationVer2()

' This Macro generates all possible combinations of flow distribution
' between pumps to meet required System Flow.
' Divisions of 0.1m(^3)are used.

' Please feed in the necessary parameters before running the Macro.

' Good Day!


'''''''''''''''''''''''' Declarations
'''''''''''''''''''''''''''''''''''''''''''

Dim x As Double

Dim Qsummax As Double

Dim Qsys, Q1, Q2, Q3, Q4, Q5 As Double

' Upper boundary conditions for flows
Dim Q1max, Q2max, Q3max, Q4max, Q5max As Double

' Lower boundary conditions for flows
Dim Q1min, Q2min, Q3min, Q4min, Q5min As Double

' Counters
Dim b, c, d, e, f As Double

' Temporary variables
Dim Qtemp As Double


'''''''''''''''''''''''' Initialization
'''''''''''''''''''''''''''''''''''''''''''



' Assigning System Flow
Qsys = Round(Range("Interface!B6"), 1)

''''''''''''''''''''''''''''''''''''''''''''''''' TO BE REMOVED -TESTING
ONLY

Qsys = 0.6

' Assigning Boundary Flow Values
Q1max = Range("Input!B10")
Q2max = Range("Input!C10")
Q3max = Range("Input!D10")
Q4max = Range("Input!E10")
Q5max = Range("Input!F10")


Q1min = Range("Input!B11")
Q2min = Range("Input!C11")
Q3min = Range("Input!D11")
Q4min = Range("Input!E11")
Q5min = Range("Input!F11")


' Assigning Counter Initial Values (To be placed after Qmin initialization)
b = Q1min
c = Q2min
d = Q3min
e = Q4min
f = Q5min



x = 4

'''''''''''''''''''''''' Main Body
''''''''''''''''''''''''''''''''''''''''''''''''


Qsummax = Q1max + Q2max + Q3max + Q4max + Q5max

If (Qsys Qsummax) Then

MsgBox "System Flow requirements exceed total pump capacity. Please run all
pumps at full speed. Insufficient System Flow is being provided at present."


Else

Do While Application.Round(b, 1) <= Q1max And _
(Qsys - b - c - d - e - f) -0.001 Or _
(Qsys - b - Q2min - Q3min - Q4min - Q5min) -0.001

Do While Application.Round(c, 1) <= Q2max And _
(Qsys - b - c - d - e - f) = -0.001 Or _
(Qsys - Q1min - c - Q3min - Q4min - Q5min) -0.001

Do While Application.Round(d, 1) <= Q3max And _
(Qsys - b - c - d - e - f) = -0.001 Or _
(Qsys - Q1min - Q2min - d - Q4min - Q5min) -0.001

Do While Application.Round(e, 1) <= Q4max And _
(Qsys - b - c - d - e - f) = 0.001 Or _
(Qsys - Q1min - Q2min - Q3min - e - Q5min) -0.001

Do While Application.Round(f, 1) <= Q5max And _
(Qsys - b - c - d - e - f) = -0.001 Or _
(Qsys - Q1min - Q2min - Q3min - Q4min - f) -0.001

If Abs(Qsys - b - c - d - e - f) < 0.001 Then
Cells(x, 1) = b
Cells(x, 2) = c
Cells(x, 3) = d
Cells(x, 4) = e
Cells(x, 5) = f

Cells(x, 7) = b + c + d + e + f
'Debug.Print x, b, c, d, e, f, b + c + d + e + f



x = x + 1

End If

f = f + 0.1
Loop
f = Q5min

e = e + 0.1
Loop
e = Q4min


d = d + 0.1
Loop
d = Q3min


c = c + 0.1
Loop
c = Q2min

b = b + 0.1
Loop
b = Q1min

End If

End Sub


--
Regards,
Tom Ogilvy

"highjumper" wrote
in message ...

anyone? :(


--
highjumper
------------------------------------------------------------------------
highjumper's Profile:
http://www.excelforum.com/member.php...o&userid=26516
View this thread: http://www.excelforum.com/showthread...hreadid=398664

Thanks a lot for your help! I will test out the code this instant!
Thanks again! :)


--
highjumper
------------------------------------------------------------------------
highjumper's Profile: http://www.excelforum.com/member.php...o&userid=26516
View this thread: http://www.excelforum.com/showthread...hreadid=398664