Convert If..Else to Select Case Statement.
 

Hi there,

I have a problem in using the if..else statement. The
conditions which is set within the statement does not
seem to apply on all the values which is keyed in. I have
checked the formulas so many times and the formulas are
correct. I guess it must be the sructure which is causing
the problem.

Now, I have included part of the code on the if..else
statement below. Alternatively, now I want to use Select
Case statement. How do I convert the code below to the
Select Case statement? I'm new to this VBA so I hope you
would excuse me if my question sounds silly to you.

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

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

ElseIf (txtCalculatedDemand.Text txtDtotal.Text) Then
txtFirmStandbyCharge.Text = "0"
txtFirmStandbyCharge.Value = Format
(txtFirmStandbyCharge.Value, "###,###.##")

Else
txtFirmStandbyCharge.Text = "0"
End If

'Firm - Penalty Bill
If (txtDmdPeak.Text txtDtotal.Text) And
(txtCalculatedDemand.Value < (txtDsbf.Value +
(txtDmdPeak.Value * 1.25) - txtDtotal.Value)) Then
txtFirmStandbyCharge.Text = (((txtDsbf.Value +
(txtDmdPeak.Value * 1.25) - txtDtotal.Value) -
txtCalculatedDemand.Value) *
txtMaxDmcFirm.Value)
txtFirmStandbyCharge.Value = Format
(txtFirmStandbyCharge.Value, "##,###.##")

ElseIf (txtDsbf.Text < txtCalculatedDemand.Text <
((txtDsbf.Value + (txtDmdPeak.Value * 1.25) -
txtDtotal.Value) + txtDsbnf.Value)) Then
txtFirmStandbyCharge.Text = (txtDsbf.Value +
(txtDsbf.Value + (txtDmdPeak.Value * 1.25) -
txtDtotal.Value) -
txtCalculatedDemand.Value) * txtMaxDmcFirm.Value
txtFirmStandbyCharge.Value = Format
(txtFirmStandbyCharge.Value, "##,###.##")

ElseIf (txtCalculatedDemand.Value ((txtDsbf.Value +
(txtDmdPeak.Value * 1.25) - txtDtotal.Value) +
txtDsbnf.Value)) Then
txtFirmStandbyCharge.Text = "0"
txtFirmStandbyCharge.Value = Format
(txtFirmStandbyCharge.Value, "##,###.##")
Else
txtFirmStandbyCharge.Text = "0"
End If

'================================================= ========
'NonFirm - Normal Bill
If (txtDmdPeak.Text <= txtDtotal.Text) And (Answer =
True) And (txtCalculatedDemand.Text < txtDsbf.Text) Then
txtNonFirmStandbyCharge.Text = txtDsbnf.Value *
txtMaxDmcNonFirm.Value
txtNonFirmStandbyCharge.Value = Format
(txtNonFirmStandbyCharge.Value, "###,###.##")

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

'NonFirm - Penalty Bill
If (txtCalculatedDemand.Text < (txtDsbf.Value +
(txtDmdPeak.Value * 1.25) - txtDtotal.Value)) Then
txtNonFirmStandbyCharge.Text = txtDsbnf.Value *
txtMaxDmcNonFirm.Value
txtNonFirmStandbyCharge.Value = Format
(txtNonFirmStandbyCharge.Value, "###,###.##")

ElseIf (((txtDsbf.Value + (txtDmdPeak.Value * 1.25) -
txtDtotal.Value)) < txtCalculatedDemand.Value) And
(txtCalculatedDemand.Text <
(((txtDsbf.Value + (txtDmdPeak.Value * 1.25) -
txtDtotal.Text)) + txtDsbnf.Value)) Then
txtNonFirmStandbyCharge.Text =
(txtDsbnf.Value + ((txtDsbf.Value + (txtDmdPeak.Value *
1.25) - txtDtotal.Value)) -
txtCalculatedDemand.Value) *
txtMaxDmcNonFirm.Value
txtNonFirmStandbyCharge.Value = Format
(txtNonFirmStandbyCharge.Value, "###,###.##")

ElseIf ((((txtDsbf.Value + (txtDmdPeak.Value * 1.25) -
txtDtotal.Value)) + txtDsbnf.Value) <
txtCalculatedDemand.Text) Then
txtNonFirmStandbyCharge.Text = "0"

End If
'================================================= ========

Well, I understand that this coding is meaningless to you
but I would appreciate if you could look into the
structure.

Thank you very much.

Sheela

Convert If..Else to Select Case Statement.
 

Sheela,

Select Case is not going to simplify your tests. It is useful when testing a single calculated value against various results, not
when the logic of each test varies. You would do better to persevere with If.

You appear to be taking numeric values from text boxes. By default, these values are string values. This should not be a problem as
VBA will coerce the text into numeric values in most cases. However, this could be a problem in some circumstances and it might be a
good idea to convert each text value to a numeric value using the Val or Cdbl functions. That is, use Val(txtDmdPeak.Text) instead
of txtDmdPeak.Text, for example.

--

John Green - Excel MVP
Sydney
Australia


"Sheela" wrote in message ...
Hi there,

I have a problem in using the if..else statement. The
conditions which is set within the statement does not
seem to apply on all the values which is keyed in. I have
checked the formulas so many times and the formulas are
correct. I guess it must be the sructure which is causing
the problem.

Now, I have included part of the code on the if..else
statement below. Alternatively, now I want to use Select
Case statement. How do I convert the code below to the
Select Case statement? I'm new to this VBA so I hope you
would excuse me if my question sounds silly to you.

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

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

ElseIf (txtCalculatedDemand.Text txtDtotal.Text) Then
txtFirmStandbyCharge.Text = "0"
txtFirmStandbyCharge.Value = Format
(txtFirmStandbyCharge.Value, "###,###.##")

Else
txtFirmStandbyCharge.Text = "0"
End If

'Firm - Penalty Bill
If (txtDmdPeak.Text txtDtotal.Text) And
(txtCalculatedDemand.Value < (txtDsbf.Value +
(txtDmdPeak.Value * 1.25) - txtDtotal.Value)) Then
txtFirmStandbyCharge.Text = (((txtDsbf.Value +
(txtDmdPeak.Value * 1.25) - txtDtotal.Value) -
txtCalculatedDemand.Value) *
txtMaxDmcFirm.Value)
txtFirmStandbyCharge.Value = Format
(txtFirmStandbyCharge.Value, "##,###.##")

ElseIf (txtDsbf.Text < txtCalculatedDemand.Text <
((txtDsbf.Value + (txtDmdPeak.Value * 1.25) -
txtDtotal.Value) + txtDsbnf.Value)) Then
txtFirmStandbyCharge.Text = (txtDsbf.Value +
(txtDsbf.Value + (txtDmdPeak.Value * 1.25) -
txtDtotal.Value) -
txtCalculatedDemand.Value) * txtMaxDmcFirm.Value
txtFirmStandbyCharge.Value = Format
(txtFirmStandbyCharge.Value, "##,###.##")

ElseIf (txtCalculatedDemand.Value ((txtDsbf.Value +
(txtDmdPeak.Value * 1.25) - txtDtotal.Value) +
txtDsbnf.Value)) Then
txtFirmStandbyCharge.Text = "0"
txtFirmStandbyCharge.Value = Format
(txtFirmStandbyCharge.Value, "##,###.##")
Else
txtFirmStandbyCharge.Text = "0"
End If

'================================================= ========
'NonFirm - Normal Bill
If (txtDmdPeak.Text <= txtDtotal.Text) And (Answer =
True) And (txtCalculatedDemand.Text < txtDsbf.Text) Then
txtNonFirmStandbyCharge.Text = txtDsbnf.Value *
txtMaxDmcNonFirm.Value
txtNonFirmStandbyCharge.Value = Format
(txtNonFirmStandbyCharge.Value, "###,###.##")

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

'NonFirm - Penalty Bill
If (txtCalculatedDemand.Text < (txtDsbf.Value +
(txtDmdPeak.Value * 1.25) - txtDtotal.Value)) Then
txtNonFirmStandbyCharge.Text = txtDsbnf.Value *
txtMaxDmcNonFirm.Value
txtNonFirmStandbyCharge.Value = Format
(txtNonFirmStandbyCharge.Value, "###,###.##")

ElseIf (((txtDsbf.Value + (txtDmdPeak.Value * 1.25) -
txtDtotal.Value)) < txtCalculatedDemand.Value) And
(txtCalculatedDemand.Text <
(((txtDsbf.Value + (txtDmdPeak.Value * 1.25) -
txtDtotal.Text)) + txtDsbnf.Value)) Then
txtNonFirmStandbyCharge.Text =
(txtDsbnf.Value + ((txtDsbf.Value + (txtDmdPeak.Value *
1.25) - txtDtotal.Value)) -
txtCalculatedDemand.Value) *
txtMaxDmcNonFirm.Value
txtNonFirmStandbyCharge.Value = Format
(txtNonFirmStandbyCharge.Value, "###,###.##")

ElseIf ((((txtDsbf.Value + (txtDmdPeak.Value * 1.25) -
txtDtotal.Value)) + txtDsbnf.Value) <
txtCalculatedDemand.Text) Then
txtNonFirmStandbyCharge.Text = "0"

End If
'================================================= ========

Well, I understand that this coding is meaningless to you
but I would appreciate if you could look into the
structure.

Thank you very much.

Sheela