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