Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If..Else Statement in Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If..Else Statement in Excel
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 "Sheela" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If..Else Statement in Excel
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 Sheela wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If..Else Statement in Excel
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 "Sheela" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If..Else Statement in Excel
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 "Sheela" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
If..Else Statement in Excel
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 -----Original Message----- 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 "Sheela" wrote in message ... 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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
If..Else Statement in Excel
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" wrote in message ... 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 <snip |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
If..Else Statement in Excel
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 (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" wrote in message ... 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 <snip . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If statement in excel | Excel Discussion (Misc queries) | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions |