If - End if problem

[Papa Jonah]

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

 

[Ian]

"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

 

[Conan Kelly]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

 

[Papa Jonah]

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\)]

Did you try what I posted (it does work)?

Rick

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.

 

[Conan Kelly]

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

 

[Conan Kelly]

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

 

[Ron Rosenfeld]

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

 

[Papa Jonah]

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

 

[Ron Rosenfeld]

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

 

[Papa Jonah]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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

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