Hi all,

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

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


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

Many thanks for your kind attention and i await your reply in 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!


'''''''''''''''''''''''' 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

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 Sub