Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with a program code
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code for program run once everyday | Excel Programming | |||
Add code to a excel's form created by program (Vb .Net) | New Users to Excel | |||
[HELP] closing a program with VBA code | Excel Programming | |||
vba program problem | Excel Programming | |||
Searching for VB Code to Link to Program | Excel Programming |