If..Else Statement in Excel

  • Thread starter Thread starter Sheela
  • Start date Start date
S

Sheela

What
is max condition can u have in an If...Else Statement. I
have four conditions. How do I apply the if...else
statement on this conditions?

As what I know and have tried, in an if..else statement,
we can only have one "else if" and one "else" within the
structure. I hope I'm not being confusing.

I have include the code:

=====================================================
If txtDmdPeak.Text <= txtDtotal.Text Then
'Firm - Normal Bill
If Answer = True Then
If txtCalculatedDemand.Text < txtDsbf.Text Then
txtFirmStandbyCharge.Text = (txtDsbf.Value -
txtCalculatedDemand.Value) * txtMaxDmcFirm.Value
If txtFirmStandbyCharge.Value = "" Then
txtFirmStandbyCharge.Value = "0"
Else
txtFirmStandbyCharge.Value = Format
(txtFirmStandbyCharge.Value, "######.##")
End If
End If

If txtDsbf.Text < txtCalculatedDemand.Text Then
If txtCalculatedDemand.Text < txtDtotal.Text
Then
txtFirmStandbyCharge.Text =
(txtDtotal.Value -
txtCalculatedDemand.Value) * txtMaxDmcNonFirm.Value
If txtFirmStandbyCharge.Text = "" Then
txtFirmStandbyCharge.Text = "0"
Else
txtFirmStandbyCharge.Value = Format
(txtFirmStandbyCharge.Value, "######.##")
End If
End If
End If
==========================================================
I have posted this question earlier but there wasn't any
reply to this. The only advise was replied to me was that
I should replace the inner if to else if. But again, pls
verify the statement that I made in my first & second
paragraph.

Thank you very much in advance for your help.

Sheela
 
Sheela,
I'll look more closely at your code, but it may not be easy to give the best
advice if the conditions are not clear.

I don't know the limit on nested Ifs, but you can probabaly have more than
you'd care to look at. The following If-ElseIf-Else stetement is valid. I am
not sure how many ElseIfs you can have but I believe the limit is greater
than any legitimate need.

If condition Then
'do something
ElseIf condition Then
'do something
ElseIf condition Then
'do something
ElseIf condition Then
'do something
ElseIf condition Then
'do something
Else 'in any case
'do something
End If

You can combine conditions also
If condition1 And condition2 And condition3 And ... Then
'do something
End If

Sometimes a Select Case is appropriate in place of an If Then Else.

Select Case var
Case is "1"
Case is "a fish"
'etc.
Case Else
End Select

Bob Kilmer
 
Sub Tester9()
Dim k As Long, i As Long, j As Long
For i = 1 To 16
If i = 1 Then
j = 1
ElseIf i = 2 Then
j = 2
ElseIf i = 3 Then
j = 3
ElseIf i = 4 Then
j = 4
ElseIf i = 5 Then
j = 5
ElseIf i = 6 Then
j = 6
ElseIf i = 7 Then
j = 7
ElseIf i = 8 Then
j = 8
ElseIf i = 9 Then
j = 9
ElseIf i = 10 Then
j = 10
ElseIf i = 11 Then
j = 11
ElseIf i = 12 Then
j = 12
ElseIf i = 13 Then
j = 13
ElseIf i = 14 Then
j = 14
ElseIf i = 15 Then
j = 15
Else
j = 16
End If
Debug.Print i, j
Next
End Sub

That is at least 14 elseif.

You can only have one else.

Regards,
Tom Ogilvy
 
I believe the following is equivalent to the code you posted:

Sub Main()
If txtDP.Text <= txtD.Text Then
'Firm - Normal Bill
If Answer = True Then

If txtCD.Text < txtD.Text Then
txtFSC.Text = (txtD.Value - txtCD.Value) * txtMDF.Value
ElseIf txtD.Text < txtCD.Text Then
If txtCD.Text < txtDT.Text Then
txtFSC.Text = (txtDT.Value - txtCD.Value) * txtMDNF.Value
End If
End If

'I pulled this out because you do it in both cases.
'It could go all the way out depending on exactly what you are doing..
If Len(txtFSC.Text) < 1 Then
txtFSC.Text = "0"
Else
txtFSC.Value = Format(txtFSC.Value, "######.##")
End If

End If
End If

End Sub


The first three conditions could be combined if no code comes between them.

Sub Main()
If (txtDP.Text <= txtD.Text) And (Answer = True) And (txtCD.Text <
txtD.Text) Then
txtFSC.Text = (txtD.Value - txtCD.Value) * txtMDF.Value
ElseIf txtD.Text < txtCD.Text Then
If txtCD.Text < txtDT.Text Then
txtFSC.Text = (txtDT.Value - txtCD.Value) * txtMDNF.Value
End If
End If

If Len(txtFSC.Text) < 1 Then
txtFSC.Text = "0"
Else
txtFSC.Value = Format(txtFSC.Value, "######.##")
End If
End Sub

Bob Kilmer
 
Or perhaps,

Sub Main()

If (txtDP.Text <= txtD.Text) And (Answer = True) And (txtCD.Text <
txtD.Text) Then
txtFSC.Text = (txtD.Value - txtCD.Value) * txtMDF.Value
ElseIf (txtD.Text < txtCD.Text) And (txtCD.Text < txtDT.Text) Then
txtFSC.Text = (txtDT.Value - txtCD.Value) * txtMDNF.Value
End If

If Len(txtFSC.Text) < 1 Then
txtFSC.Text = "0"
Else
txtFSC.Value = Format(txtFSC.Value, "######.##")
End If

End Sub

Bob Kilmer
 
Is this structure correct. I've made some changes and
followed like what you suggested but the results is still
not displaying on the screen.
---------------------------------------------------
If txtDmdPeak.Text <= txtDtotal.Text Then
If txtCalculatedDemand.Text < txtDsbf.Text Then
txtFirmStandbyCharge.Text = (txtDsbf.Value -
txtCalculatedDemand.Value) * txtMaxDmcFirm.Value
If txtFirmStandbyCharge.Value = "" Then
txtFirmStandbyCharge.Value = "0"
Else
txtFirmStandbyCharge.Value = Format
(txtFirmStandbyCharge.Value, "######.##")
End If
End If


ElseIf txtDsbf.Text < txtCalculatedDemand.Text Then
If txtCalculatedDemand.Text < txtDtotal.Text Then
txtFirmStandbyCharge.Text = (txtDtotal.Value -
txtCalculatedDemand.Value) * txtMaxDmcNonFirm.Value
If txtFirmStandbyCharge.Text = "" Then
txtFirmStandbyCharge.Text = "0"
Else
txtFirmStandbyCharge.Value = Format
(txtFirmStandbyCharge.Value, "######.##")
End If
End If

ElseIf txtCalculatedDemand.Text > txtDtotal.Text Then
txtFirmStandbyCharge.Text =
txtCalculatedDemandCharge.Value + txtEmp.Value +
txtEmop.Value
If txtFirmStandbyCharge.Text = "" Then
txtFirmStandbyCharge.Text = "0"
Else
txtFirmStandbyCharge.Value = Format
(txtFirmStandbyCharge.Value, "######.##")
End If
Else
txtFirmStandbyCharge.Value = "0"
End If
-----------------------------------------------------
Please help me. I'm really confused. Thank you very much
for your help.

Regards,
Sheela
 
Sheela,

The code you posted is syntactically correct (meaning it is written
according to the rules of VB). I cannot tell if the logic is correct because
I don't know the meaning of what you are trying to do. If you are still
having problems with this, I think you should focus on getting something
running, even if it is not totally correct, then step thru the code,
checking values against your assumptions at each step.

Some observations:

First, you are using Value in some places and Text in others. They are
equivalent, so to simplify, just use Text everywhere.

Second, you are using math operations on text which can have unforeseen
consequences. ***Note that strings that do not "look" like numbers to VB are
interpreted as zero.*** Strings sometimes get concatenated (get stuck
together end to end) and become an entirely unexpected number, instead of
being mathematically added together.

Ideally, all text values (which are a String data type) should be changed to
a suitable numeric type (Integer, Single, Double, Long) when you do math
operations with them and changed back to strings when assigned to a textbox
Text property. Let's assume for the time being that VB is handling this
correctly for you, which indeed it tries to do. Do keep this
math-operations-with-text idea in the back of your mind as a potential
source of failure.

Third, this snippet of code occurs in your if-ElseIf block three times:

If Len(FirmStandbyCharge) < 1 Then
txtFirmStandbyCharge.Text = "0"
Else
txtFirmStandbyCharge.Text = Format(FirmStandbyCharge, "######.##")
End If

Create a separate subroutine. Move this code there. Call the subroutine from
your If-ElseIf code in place of the code removed. This will simplify the
If-ElseIf code which will make it easier to troubleshoot.

I will post separately a modified version of your if-block that illustrates
the separate subroutine idea. I think your objective should be to get the
code simplified and syntactically correct so that it runs without complaint,
then step thru the code, examining values at each step to see if they are
what you expect.

Bob Kilmer


Sheela said:
Is this structure correct. I've made some changes and
followed like what you suggested but the results is still
not displaying on the screen.
---------------------------------------------------
If txtDmdPeak.Text <= txtDtotal.Text Then
If txtCalculatedDemand.Text < txtDsbf.Text Then
txtFirmStandbyCharge.Text = (txtDsbf.Value -
txtCalculatedDemand.Value) * txtMaxDmcFirm.Value
If txtFirmStandbyCharge.Value = "" Then
txtFirmStandbyCharge.Value = "0"
Else
txtFirmStandbyCharge.Value = Format
(txtFirmStandbyCharge.Value, "######.##")
End If
End If


ElseIf txtDsbf.Text < txtCalculatedDemand.Text Then
If txtCalculatedDemand.Text < txtDtotal.Text Then
txtFirmStandbyCharge.Text = (txtDtotal.Value -
txtCalculatedDemand.Value) * txtMaxDmcNonFirm.Value
If txtFirmStandbyCharge.Text = "" Then
txtFirmStandbyCharge.Text = "0"
Else
txtFirmStandbyCharge.Value = Format
(txtFirmStandbyCharge.Value, "######.##")
End If
End If

ElseIf txtCalculatedDemand.Text > txtDtotal.Text Then
txtFirmStandbyCharge.Text =
txtCalculatedDemandCharge.Value + txtEmp.Value +
txtEmop.Value
If txtFirmStandbyCharge.Text = "" Then
txtFirmStandbyCharge.Text = "0"
Else
txtFirmStandbyCharge.Value = Format
(txtFirmStandbyCharge.Value, "######.##")
End If
Else
txtFirmStandbyCharge.Value = "0"
End If
-----------------------------------------------------
Please help me. I'm really confused. Thank you very much
for your help.

Regards,
Sheela
Click to expand...
<snip>
 
Bob,

Can I use Select Case instead of If..Else statement? Is
Select Case is used only when we select the options that
we code or can it be done similarly to the if..else
statement whereby it calculates based on the input keyed
in?

I hope I posted the questions correctly.

thanks,

Sheela
-----Original Message-----
Sheela,

The code you posted is syntactically correct (meaning it is written
according to the rules of VB). I cannot tell if the logic is correct because
I don't know the meaning of what you are trying to do. If you are still
having problems with this, I think you should focus on getting something
running, even if it is not totally correct, then step thru the code,
checking values against your assumptions at each step.

Some observations:

First, you are using Value in some places and Text in others. They are
equivalent, so to simplify, just use Text everywhere.

Second, you are using math operations on text which can have unforeseen
consequences. ***Note that strings that do not "look" like numbers to VB are
interpreted as zero.*** Strings sometimes get concatenated (get stuck
together end to end) and become an entirely unexpected number, instead of
being mathematically added together.

Ideally, all text values (which are a String data type) should be changed to
a suitable numeric type (Integer, Single, Double, Long) when you do math
operations with them and changed back to strings when assigned to a textbox
Text property. Let's assume for the time being that VB is handling this
correctly for you, which indeed it tries to do. Do keep this
math-operations-with-text idea in the back of your mind as a potential
source of failure.

Third, this snippet of code occurs in your if-ElseIf block three times:

If Len(FirmStandbyCharge) < 1 Then
txtFirmStandbyCharge.Text = "0"
Else
txtFirmStandbyCharge.Text = Format
Click to expand...
(FirmStandbyCharge, "######.##")
 
Back
Top