Loops with If Else statement

S

sok01

New Member
Joined
Aug 22, 2003
Messages
22
Guys,

I'm running Excel 2003 on XP Pro and I'm having a problem with the code below. I'm trying to calculate either Heat reqd in kw or Outlet temp in deg. The two separate sections of code run fine on their own but I think I have the loop in the wrong place. I want the code to calculate one or the other depending on what data is available within the test table i.e. if there is NO kw heating power, calc heat reqd (kw) to give me the stated outlet temp or if there IS kw heating power, calc the outlet temp that can be achieved from this energy. Unfortunately due to restrictions on my networked PC I cant post the table in HTML format. Thanks in advance.

Sorry ...... here it is in code form.



Code: 
Function HeatInTable()

'Application.ScreenUpdating = False
' this routine will calculate heat reqd(kw) or outlet temp in degC
' the input reqd are all metric


' list of all variables used
Dim A, B, c, D, E1, E2, E3, E4, E5, E6, P1, P2 As Variant
Dim HEAT, Q, Tin, H1in, ERFin, ENTHIN, Tout As Variant
Dim H1out, ERFout, ENTHOUT, DELTAH, MOL As Variant

' set parameters
A = -0.00792078
B = 950.85
c = 0.0314566
D = 0.000009138
E1 = 0.000358
E2 = 0.001391
E3 = 0.0003572
E4 = 0.000008397
E5 = 0.00000119
E6 = 0.0845
MOL = 17.18 'molecular weight of gas

' calc all variables



    If IsEmpty(Range("I12").Offset(0, 6)) = True Then
   
            
            For R = 12 To 25
    
            Range("I" & R).Select
    
            P1 = Range("I" & R).Offset(0, 1) + 14.7 ' inlet pressure
            P2 = Range("I" & R).Offset(0, 2) + 14.7 ' outlet pressure
            Q = Range("I" & R).Offset(0, 3) * (MOL / 3) / 28.31682051 'calc and conversion to Kscmh
            Tin = Range("I" & R).Offset(0, 4) + 273.15 'inlet temperature
            H1in = (c - E1) * Tin + D * (Tin ^ 2) + (A - E2) * P1 - B * P1 / (Tin ^ 2)
            ERFin = -E3 * (P1 ^ 2) + E4 * P1 * Tin + E5 * Tin * (P1 ^ 2) + E6
            ENTHIN = H1in + ERFin
            Tout = Range("I" & R).Offset(0, 5) + 273.15
            H1out = (c - E1) * Tout + D * (Tout ^ 2) + (A - E2) * P2 - B * P2 / (Tout ^ 2)
            ERFout = -E3 * (P2 ^ 2) + E4 * P2 * Tout + E5 * Tout * (P2 ^ 2) + E6
            ENTHOUT = H1out + ERFout
            DELTAH = ENTHIN - ENTHOUT ' enthalpy difference
    
            ' calculate heat reqd in Kw's
            HEAT = Int(-DELTAH * Q * (1000 / MOL))
            Range("I" & R).Offset(0, 7) = HEAT
            Next
        
    ElseIf IsEmpty(Range("I12").Offset(0, 7)) = True Then
   
    
    For R = 12 To 25
    Range("I" & R).Select
    
        Dim Check, Counter
        Check = True: Counter = -20    ' Initialize variables.
    
            Do While Counter < 25
                Counter = Counter + 0.1    ' Increment Counter.
              
                
                P1 = Range("I" & R).Offset(0, 1) + 14.7  ' inlet pressure
                P2 = Range("I" & R).Offset(0, 2) + 14.7 ' outlet pressure
                Q = Range("I" & R).Offset(0, 3) * (MOL / 3) / 28.31682051 'calc and conversion to Kscmh
                Tin = Range("I" & R).Offset(0, 4) + 273.15 'inlet temperature
                H1in = (c - E1) * Tin + D * (Tin ^ 2) + (A - E2) * P1 - B * P1 / (Tin ^ 2)
                ERFin = -E3 * (P1 ^ 2) + E4 * P1 * Tin + E5 * Tin * (P1 ^ 2) + E6
                ENTHIN = H1in + ERFin
                Tout = Counter + 273.15 ' Outlet Temp
                H1out = (c - E1) * Tout + D * (Tout ^ 2) + (A - E2) * P2 - B * P2 / (Tout ^ 2)
                ERFout = -E3 * (P2 ^ 2) + E4 * P2 * Tout + E5 * Tout * (P2 ^ 2) + E6
                ENTHOUT = H1out + ERFout
                DELTAH = ENTHIN - ENTHOUT ' enthalpy difference
        
                ' calculate outlet temp in deg's
                HEAT = Int(-DELTAH * Q * (1000 / MOL))
                Range("I" & R).Offset(0, 5) = Tout - 273.15 'display the outlet temp
                
                If Int(HEAT) > Range("I" & R).Offset(0, 6) Then ' If condition is True.
                    Check = False    ' Set value of flag to False and exit loop
                    Exit Do
                End If
            Loop
    
    
    Next

End If
'Application.ScreenUpdating = True
End Function
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
One you get to this amount of code it is necessary to format it correctly and add comments. Especially using the tab key to indent lines of code so that the beginning and ends of groups is clear. Attempts at shortcuts are a waste of time. I can go back to code I have written months ago and make changes in a couple of minutes.

You can see I have also kept the formatting in this message by selecting the text and clicking the "Code" button above the message box.

Code: 
Sub test()
    Dim
    Dim
    Dim
    '----------------------------------
    If
    
    
    ElseIf
        For r = 12 To 25
            Do While
            
            Loop
        Next
    End If
End Sub

Here is a sample of correctly formatted code. See how much easier it is to read (and debug):-

http://www.mrexcel.com/board2/viewtopic.php?t=148374&highlight=
 
Upvote 0
Hey,

I know this post was made ages ago, however I have a question. It is not regarding the actual programming of the system but more the thermodynamics that is being used.

I was wondering if anyone had any information regarding the figures that are used under the heading set parameters. I dont really understand what parameters they are and where they have come from

Thanks,
Aisers
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
IF dates
Replies
9
Views
293
BrutalLogiC
B
E
  • Solved
Get a 2D spilled formula to fulfill a distribution table matching the ranges of both dimensions
Replies
3
Views
261
Fluff
Fluff
M
  • Solved
Adding a NR to a formula & changing an IF to an IFS statement.
Replies
3
Views
290
Marvo
M
gtd526
  • Question Question
macro to add correct number of ")" at end of nested IF statement
Replies
1
Views
301
gtd526
gtd526
Young Grasshopper
  • Solved
Excel VBA / Loop to copy unknown number of columns underneath each other, and string to each cell, AND adjecent cells!
Replies
4
Views
836
kevin9999
K

Forum statistics

Threads
1,222,910
Messages
6,168,998
Members
452,229
Latest member
mjohny

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top