Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select Case Statement | Excel Worksheet Functions | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
excel'03 how to convert a column from upper case to proper case | Excel Discussion (Misc queries) | |||
Convert lower case charecters to upper case | Excel Discussion (Misc queries) | |||
Data validation with the Select Case statement | Excel Programming |