Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If condition
Is there a way to have this condition read in this one line.
IF cells(valid,13)"" and (cells(valid,19)""and ((cells(valid,19)-cells(valid,13))< 20 then cells(valid,30)=cells(valid,8)&CEDissue I am sure I am not following the correct syntax for the nested if but I am having a hard time finding the correct syntax in this case. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If condition
Personally, I think one-liner If-Then statements should be confined to when
the entire statement would be **short** (otherwise it will be hard to read when you come back to your code six months from now to modify it) and your code line does not qualify, so I would write it this way... If Cells(Valid, 13) < "" And Cells(Valid, 19) < "" And _ (Cells(Valid, 19) - Cells(Valid, 13)) < 20 Then Cells(Valid, 30) = Cells(Valid, 8) & CEDissue End If However, if you are insistent in listing it on one line, this what it would look like (your newsreader will probably wrap the line because of it length, so you will have to account for that) If Cells(Valid, 13) < "" And Cells(Valid, 19) < "" And (Cells(Valid, 19) - Cells(Valid, 13)) < 20 Then Cells(Valid, 30) = Cells(Valid, 8) & CEDissue By the way, note that I changed your cell value tests of greater than "" to not equal to "" which is what I assumed you were trying to check. Rick "Rpettis31" wrote in message ... Is there a way to have this condition read in this one line. IF cells(valid,13)"" and (cells(valid,19)""and ((cells(valid,19)-cells(valid,13))< 20 then cells(valid,30)=cells(valid,8)&CEDissue I am sure I am not following the correct syntax for the nested if but I am having a hard time finding the correct syntax in this case. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If condition
This should do it
If Cells(Valid, 13) "" And Cells(Valid, 19) "" _ And Cells(Valid, 19) - Cells(Valid, 13) < 20 Then Cells(Valid, 30) = Cells(Valid, 8) & cedissue End If Mike "Rpettis31" wrote: Is there a way to have this condition read in this one line. IF cells(valid,13)"" and (cells(valid,19)""and ((cells(valid,19)-cells(valid,13))< 20 then cells(valid,30)=cells(valid,8)&CEDissue I am sure I am not following the correct syntax for the nested if but I am having a hard time finding the correct syntax in this case. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If condition
The only reason I was doing the one liner was this is in a loop and kept
giving me an error Next without for. I am assuming the _ takes care of that issue. "Rick Rothstein (MVP - VB)" wrote: Personally, I think one-liner If-Then statements should be confined to when the entire statement would be **short** (otherwise it will be hard to read when you come back to your code six months from now to modify it) and your code line does not qualify, so I would write it this way... If Cells(Valid, 13) < "" And Cells(Valid, 19) < "" And _ (Cells(Valid, 19) - Cells(Valid, 13)) < 20 Then Cells(Valid, 30) = Cells(Valid, 8) & CEDissue End If However, if you are insistent in listing it on one line, this what it would look like (your newsreader will probably wrap the line because of it length, so you will have to account for that) If Cells(Valid, 13) < "" And Cells(Valid, 19) < "" And (Cells(Valid, 19) - Cells(Valid, 13)) < 20 Then Cells(Valid, 30) = Cells(Valid, 8) & CEDissue By the way, note that I changed your cell value tests of greater than "" to not equal to "" which is what I assumed you were trying to check. Rick "Rpettis31" wrote in message ... Is there a way to have this condition read in this one line. IF cells(valid,13)"" and (cells(valid,19)""and ((cells(valid,19)-cells(valid,13))< 20 then cells(valid,30)=cells(valid,8)&CEDissue I am sure I am not following the correct syntax for the nested if but I am having a hard time finding the correct syntax in this case. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If condition
The _ (when it follows a space and is at the end of the line) is a line
continuation character... it means the line below will be treated as a continuation the line in which it appears. It is useful to keep your long statements from running out of view on the right side of the code window. It is particularly useful in newsgroup postings when applied within a newsreader's line wrapping length as it allows the code to be copy/pasted exactly as posted. Here is a shorter example to show you what I mean... this Combo = String1 & String2 is exactly equivalent to this.... Combo = String1 & _ String2 Note: You can't use line continuation with a String constant... you must break it apart and line continue the concatenated parts. For example, if you wanted to line continue this assignment... TextVariable = "This is a line of text to demonstrate what I mean" You **cannot** do it this way... TextVariable = "This is a line of text to _ demonstrate what I mean" Nor can you do it this way either... TextVariable = "This is a line of text to" _ "demonstrate what I mean" Rather, you would have to do it this way... TextVariable = "This is a line of text to " & _ "demonstrate what I mean" Rick "Rpettis31" wrote in message ... The only reason I was doing the one liner was this is in a loop and kept giving me an error Next without for. I am assuming the _ takes care of that issue. "Rick Rothstein (MVP - VB)" wrote: Personally, I think one-liner If-Then statements should be confined to when the entire statement would be **short** (otherwise it will be hard to read when you come back to your code six months from now to modify it) and your code line does not qualify, so I would write it this way... If Cells(Valid, 13) < "" And Cells(Valid, 19) < "" And _ (Cells(Valid, 19) - Cells(Valid, 13)) < 20 Then Cells(Valid, 30) = Cells(Valid, 8) & CEDissue End If However, if you are insistent in listing it on one line, this what it would look like (your newsreader will probably wrap the line because of it length, so you will have to account for that) If Cells(Valid, 13) < "" And Cells(Valid, 19) < "" And (Cells(Valid, 19) - Cells(Valid, 13)) < 20 Then Cells(Valid, 30) = Cells(Valid, 8) & CEDissue By the way, note that I changed your cell value tests of greater than "" to not equal to "" which is what I assumed you were trying to check. Rick "Rpettis31" wrote in message ... Is there a way to have this condition read in this one line. IF cells(valid,13)"" and (cells(valid,19)""and ((cells(valid,19)-cells(valid,13))< 20 then cells(valid,30)=cells(valid,8)&CEDissue I am sure I am not following the correct syntax for the nested if but I am having a hard time finding the correct syntax in this case. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
If condition
The only reason I was doing the one liner was this is in a loop and kept
giving me an error Next without for. Just a follow-up on your comment: The error message you were getting is a catchall that pops up when you have block If ... Then in combination with For ... Next statements and one or the other is missing its closer of End If or Next. In your case, you had probably written an If...Then statement with the Then condition on the second line. That would make the compiler look for and End If since the second line indicates the block format. However, It found the Next and when it went back to find the for, it found an If and assumed the Next was the hanging chad. Sometimes it will tell you that you have an block If without an End If and the actual error will be a missing End With. When you have embedded If ... Then statements inside For .... Next or With...End With statements, you have to check each level of statements to find the actual missing closer. This is one that Microsoft has never taken the time to resolve. "Rpettis31" wrote: The only reason I was doing the one liner was this is in a loop and kept giving me an error Next without for. I am assuming the _ takes care of that issue. "Rick Rothstein (MVP - VB)" wrote: Personally, I think one-liner If-Then statements should be confined to when the entire statement would be **short** (otherwise it will be hard to read when you come back to your code six months from now to modify it) and your code line does not qualify, so I would write it this way... If Cells(Valid, 13) < "" And Cells(Valid, 19) < "" And _ (Cells(Valid, 19) - Cells(Valid, 13)) < 20 Then Cells(Valid, 30) = Cells(Valid, 8) & CEDissue End If However, if you are insistent in listing it on one line, this what it would look like (your newsreader will probably wrap the line because of it length, so you will have to account for that) If Cells(Valid, 13) < "" And Cells(Valid, 19) < "" And (Cells(Valid, 19) - Cells(Valid, 13)) < 20 Then Cells(Valid, 30) = Cells(Valid, 8) & CEDissue By the way, note that I changed your cell value tests of greater than "" to not equal to "" which is what I assumed you were trying to check. Rick "Rpettis31" wrote in message ... Is there a way to have this condition read in this one line. IF cells(valid,13)"" and (cells(valid,19)""and ((cells(valid,19)-cells(valid,13))< 20 then cells(valid,30)=cells(valid,8)&CEDissue I am sure I am not following the correct syntax for the nested if but I am having a hard time finding the correct syntax in this case. Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
If condition
Where I said embedded should have been nested.
"Rpettis31" wrote: The only reason I was doing the one liner was this is in a loop and kept giving me an error Next without for. I am assuming the _ takes care of that issue. "Rick Rothstein (MVP - VB)" wrote: Personally, I think one-liner If-Then statements should be confined to when the entire statement would be **short** (otherwise it will be hard to read when you come back to your code six months from now to modify it) and your code line does not qualify, so I would write it this way... If Cells(Valid, 13) < "" And Cells(Valid, 19) < "" And _ (Cells(Valid, 19) - Cells(Valid, 13)) < 20 Then Cells(Valid, 30) = Cells(Valid, 8) & CEDissue End If However, if you are insistent in listing it on one line, this what it would look like (your newsreader will probably wrap the line because of it length, so you will have to account for that) If Cells(Valid, 13) < "" And Cells(Valid, 19) < "" And (Cells(Valid, 19) - Cells(Valid, 13)) < 20 Then Cells(Valid, 30) = Cells(Valid, 8) & CEDissue By the way, note that I changed your cell value tests of greater than "" to not equal to "" which is what I assumed you were trying to check. Rick "Rpettis31" wrote in message ... Is there a way to have this condition read in this one line. IF cells(valid,13)"" and (cells(valid,19)""and ((cells(valid,19)-cells(valid,13))< 20 then cells(valid,30)=cells(valid,8)&CEDissue I am sure I am not following the correct syntax for the nested if but I am having a hard time finding the correct syntax in this case. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup with multiple condition, but one condition to satisfy is en | Excel Worksheet Functions | |||
Combine an OR condition with an AND condition | Excel Discussion (Misc queries) | |||
Condition 1 overules condition 2? | Excel Worksheet Functions | |||
If condition | Excel Programming | |||
I need 4 condition for condition formatting | Excel Programming |