Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If - End if problem
What is wrong with this?
If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then _ Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If I keep getting an End If without If block error. I don't understand the help. I have an If statement and and End If statement. Papa J |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If - End if problem
"Block if" is where you have
If this condition then Do this Else Do this End if Your If statement is all on one line, so isn't considered a block eg If this condition then do this Ian "Papa Jonah" wrote in message ... What is wrong with this? If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then _ Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If I keep getting an End If without If block error. I don't understand the help. I have an If statement and and End If statement. Papa J |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If - End if problem
Papa Jonah,
Use "ElseIf" instead of "Else: If" and "Else" instead of "Else:" Also, it looks like you are trying to do If...Then...ElseIf...Then...Else all on one line. If I remember correctly, this is possible and you don't need the "End If" to terminate it (don't know if it will ignore it if it is there....haven't tried that). To do it on multiple lines, use this ("End If" is required): If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else Selection.Interior.ColorIndex = 9 End If HTH, Conan "Papa Jonah" wrote in message ... What is wrong with this? If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then _ Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If I keep getting an End If without If block error. I don't understand the help. I have an If statement and and End If statement. Papa J |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If - End if problem
You will do yourself the biggest favor if you stop trying to cram all of
your If-Then-Else blocks into one line... trust me on that. Also, when you have to do future edits to your code, not cramming everything into one line will make reading your code much easier. Consider this rearrangement of your code... If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else Selection.Interior.ColorIndex = 9 End If Which do you think will be easier to work with 6 months down the road when you have to modify your code for some reason? You definitely should use the structure above, but I thought you might like to see one of several possible "true" one-line code statements that will do the same thing as the above code... Selection.Interior.ColorIndex = 9 + 6 * (locale = "P") + 3 * (locale = "L") Rick "Papa Jonah" wrote in message ... What is wrong with this? If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then _ Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If I keep getting an End If without If block error. I don't understand the help. I have an If statement and and End If statement. Papa J |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If - End if problem
I tried that but the program "corrects" it automatically to make it Else: if.
I was also getting a similar error of elses without ifs until I put it all on one line. "Conan Kelly" wrote: Papa Jonah, Use "ElseIf" instead of "Else: If" and "Else" instead of "Else:" Also, it looks like you are trying to do If...Then...ElseIf...Then...Else all on one line. If I remember correctly, this is possible and you don't need the "End If" to terminate it (don't know if it will ignore it if it is there....haven't tried that). To do it on multiple lines, use this ("End If" is required): If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else Selection.Interior.ColorIndex = 9 End If HTH, Conan "Papa Jonah" wrote in message ... What is wrong with this? If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then _ Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If I keep getting an End If without If block error. I don't understand the help. I have an If statement and and End If statement. Papa J |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
If - End if problem
I totally agree and tried that at first. All crammed together was the only
way I could stop the stupid thing from giving me "Else without If" errors. "Rick Rothstein (MVP - VB)" wrote: You will do yourself the biggest favor if you stop trying to cram all of your If-Then-Else blocks into one line... trust me on that. Also, when you have to do future edits to your code, not cramming everything into one line will make reading your code much easier. Consider this rearrangement of your code... If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else Selection.Interior.ColorIndex = 9 End If Which do you think will be easier to work with 6 months down the road when you have to modify your code for some reason? You definitely should use the structure above, but I thought you might like to see one of several possible "true" one-line code statements that will do the same thing as the above code... Selection.Interior.ColorIndex = 9 + 6 * (locale = "P") + 3 * (locale = "L") Rick "Papa Jonah" wrote in message ... What is wrong with this? If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then _ Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If I keep getting an End If without If block error. I don't understand the help. I have an If statement and and End If statement. Papa J |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
If - End if problem
Did you try what I posted (it does work)?
Rick "Papa Jonah" wrote in message ... I totally agree and tried that at first. All crammed together was the only way I could stop the stupid thing from giving me "Else without If" errors. "Rick Rothstein (MVP - VB)" wrote: You will do yourself the biggest favor if you stop trying to cram all of your If-Then-Else blocks into one line... trust me on that. Also, when you have to do future edits to your code, not cramming everything into one line will make reading your code much easier. Consider this rearrangement of your code... If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else Selection.Interior.ColorIndex = 9 End If Which do you think will be easier to work with 6 months down the road when you have to modify your code for some reason? You definitely should use the structure above, but I thought you might like to see one of several possible "true" one-line code statements that will do the same thing as the above code... Selection.Interior.ColorIndex = 9 + 6 * (locale = "P") + 3 * (locale = "L") Rick "Papa Jonah" wrote in message ... What is wrong with this? If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then _ Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If I keep getting an End If without If block error. I don't understand the help. I have an If statement and and End If statement. Papa J |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
If - End if problem
Papa Jonah,
Is this a nested If...then...else statement (is this if statement inside another if statement)? Are there if statements before/after this one? Maybe one of your other if statements is not terminated with an end if. Try: If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else Selection.Interior.ColorIndex = 9 (all one line--NO "End If") OR If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 (all one line--NO "End If") OR If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else Selection.Interior.ColorIndex = 9 End If Those 1-liners above, make sure they are truly on one line FIRST (make sure you don't do the line continuation method to break it up on multiple lines...ie. first part of statement, space, underscore, new line, rest of statement). After you get them working on one line, then go back and add in the line continuations for readability. If none of these things work, then check your other If statements to make sure they are terminated properly. Sorry I couldn't be of any more help, Conan "Papa Jonah" wrote in message ... I tried that but the program "corrects" it automatically to make it Else: if. I was also getting a similar error of elses without ifs until I put it all on one line. "Conan Kelly" wrote: Papa Jonah, Use "ElseIf" instead of "Else: If" and "Else" instead of "Else:" Also, it looks like you are trying to do If...Then...ElseIf...Then...Else all on one line. If I remember correctly, this is possible and you don't need the "End If" to terminate it (don't know if it will ignore it if it is there....haven't tried that). To do it on multiple lines, use this ("End If" is required): If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else Selection.Interior.ColorIndex = 9 End If HTH, Conan "Papa Jonah" wrote in message ... What is wrong with this? If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then _ Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If I keep getting an End If without If block error. I don't understand the help. I have an If statement and and End If statement. Papa J |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
If - End if problem
Papa Jonah,
What version of XL are you using? I'm using XL 2003 and I can't get the VBE to correct "ElseIf" to "Else: If". I get a "Compile Error: Expected: End of statement". Also, the Help document on If...Then...Else doesn't explicitly say that ElseIf can not be used in the 1-line syntax, BUT HOW IT IS WRITTEN suggests that you need to use the Block syntax if you want ElseIf's. HTH, Conan "Papa Jonah" wrote in message ... I tried that but the program "corrects" it automatically to make it Else: if. I was also getting a similar error of elses without ifs until I put it all on one line. "Conan Kelly" wrote: Papa Jonah, Use "ElseIf" instead of "Else: If" and "Else" instead of "Else:" Also, it looks like you are trying to do If...Then...ElseIf...Then...Else all on one line. If I remember correctly, this is possible and you don't need the "End If" to terminate it (don't know if it will ignore it if it is there....haven't tried that). To do it on multiple lines, use this ("End If" is required): If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else Selection.Interior.ColorIndex = 9 End If HTH, Conan "Papa Jonah" wrote in message ... What is wrong with this? If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then _ Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If I keep getting an End If without If block error. I don't understand the help. I have an If statement and and End If statement. Papa J |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
If - End if problem
On Fri, 14 Dec 2007 13:34:05 -0800, Papa Jonah
wrote: What is wrong with this? If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then _ Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If I keep getting an End If without If block error. I don't understand the help. I have an If statement and and End If statement. Papa J Is this code perhaps embedded inside a "With" block that does not have an End With? You will get that same error message under that circumstance. --ron |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
If - End if problem
Rick,
I have tried that. However, when I use "ElseIf" I still get a compile error "else without if" and highlights "ElseIf locale = "L" then" This is what I have currently: If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If When I run the macro, I am currently getting a "Compile Error - Else without If" with the first "Else:" highlighted. I don't see the difference in what I have tried and what has been suggested - but it still isn't working. Thanks for bearing with me. Papa J "Rick Rothstein (MVP - VB)" wrote: Did you try what I posted (it does work)? Rick "Papa Jonah" wrote in message ... I totally agree and tried that at first. All crammed together was the only way I could stop the stupid thing from giving me "Else without If" errors. "Rick Rothstein (MVP - VB)" wrote: You will do yourself the biggest favor if you stop trying to cram all of your If-Then-Else blocks into one line... trust me on that. Also, when you have to do future edits to your code, not cramming everything into one line will make reading your code much easier. Consider this rearrangement of your code... If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else Selection.Interior.ColorIndex = 9 End If Which do you think will be easier to work with 6 months down the road when you have to modify your code for some reason? You definitely should use the structure above, but I thought you might like to see one of several possible "true" one-line code statements that will do the same thing as the above code... Selection.Interior.ColorIndex = 9 + 6 * (locale = "P") + 3 * (locale = "L") Rick "Papa Jonah" wrote in message ... What is wrong with this? If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then _ Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If I keep getting an End If without If block error. I don't understand the help. I have an If statement and and End If statement. Papa J |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
If - End if problem
On Mon, 17 Dec 2007 14:44:34 -0800, Papa Jonah
wrote: Rick, I have tried that. However, when I use "ElseIf" I still get a compile error "else without if" and highlights "ElseIf locale = "L" then" This is what I have currently: If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If When I run the macro, I am currently getting a "Compile Error - Else without If" with the first "Else:" highlighted. I don't see the difference in what I have tried and what has been suggested - but it still isn't working. Thanks for bearing with me. Papa J This: If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If or this: If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else Selection.Interior.ColorIndex = 9 End If Both work OK here. --ron |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
If - End if problem
Apparently, after cutting and pasting yours, the only difference I see is the
indentation of yours between If and End if - but yours works. Thanks "Ron Rosenfeld" wrote: On Mon, 17 Dec 2007 14:44:34 -0800, Papa Jonah wrote: Rick, I have tried that. However, when I use "ElseIf" I still get a compile error "else without if" and highlights "ElseIf locale = "L" then" This is what I have currently: If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If When I run the macro, I am currently getting a "Compile Error - Else without If" with the first "Else:" highlighted. I don't see the difference in what I have tried and what has been suggested - but it still isn't working. Thanks for bearing with me. Papa J This: If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If or this: If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else Selection.Interior.ColorIndex = 9 End If Both work OK here. --ron |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
If - End if problem
Just as a follow up to this sub-thread... I never splice multiple commands
together using the colon operator to join them. Your attempted use of them in the If-ElseIf-Else-Then statements I posted is what caused my original code to fail you... you didn't splice them together correctly. Having been programming for some 25+ years, my advice would be for you to abandon trying to cram your If-ElseIf-Else-Then blocks together the way you showed and simply space them out (as I did in my first posting)... you will find them far more readable six months down the road when, for whatever reason, you have to modify your code. More importantly, if you (ever) work in a team shop, your co-workers who may be called on to modify your code will thank you for doing so also. Rick "Papa Jonah" wrote in message ... Apparently, after cutting and pasting yours, the only difference I see is the indentation of yours between If and End if - but yours works. Thanks "Ron Rosenfeld" wrote: On Mon, 17 Dec 2007 14:44:34 -0800, Papa Jonah wrote: Rick, I have tried that. However, when I use "ElseIf" I still get a compile error "else without if" and highlights "ElseIf locale = "L" then" This is what I have currently: If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If When I run the macro, I am currently getting a "Compile Error - Else without If" with the first "Else:" highlighted. I don't see the difference in what I have tried and what has been suggested - but it still isn't working. Thanks for bearing with me. Papa J This: If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If or this: If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else Selection.Interior.ColorIndex = 9 End If Both work OK here. --ron |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
If - End if problem
Papa J
The indentations shouldn't make a difference. If you look at what you posted, and what I posted, you will see the difference in line 3 of mine versus Line 2 of yours: PJ: Else: If locale = "L" Then Selection.Interior.ColorIndex = 6 RR: ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 As Richard pointed out, you will find it easier, in the long run, to not use the ":" at all to combine lines, but rather to put the information on separate lines. Best wishes, --ron On Tue, 18 Dec 2007 12:11:01 -0800, Papa Jonah wrote: Apparently, after cutting and pasting yours, the only difference I see is the indentation of yours between If and End if - but yours works. Thanks "Ron Rosenfeld" wrote: On Mon, 17 Dec 2007 14:44:34 -0800, Papa Jonah wrote: Rick, I have tried that. However, when I use "ElseIf" I still get a compile error "else without if" and highlights "ElseIf locale = "L" then" This is what I have currently: If locale = "P" Then Selection.Interior.ColorIndex = 3 Else: If locale = "L" Then Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If When I run the macro, I am currently getting a "Compile Error - Else without If" with the first "Else:" highlighted. I don't see the difference in what I have tried and what has been suggested - but it still isn't working. Thanks for bearing with me. Papa J This: If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else: Selection.Interior.ColorIndex = 9 End If or this: If locale = "P" Then Selection.Interior.ColorIndex = 3 ElseIf locale = "L" Then Selection.Interior.ColorIndex = 6 Else Selection.Interior.ColorIndex = 9 End If Both work OK here. --ron --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |