If Then Else with Exit For

Hi all

Not sure why my code is failing
Debug comesback highlighted at ELSE

Error is Else without IF

Thanks
-goss

Code :

Option Explicit

Sub TestValue()
Dim c As Range
Dim blnTest As Boolean

blnTest = False

For Each c In Range("C:C")
If blnTest = True Then Exit For
Else
If IsNumeric(c) Then c.EntireColumn.Hidden
blnTest = True
End If
End If
Next c
End Sub

The first If statement, If blnTest = True Then Exit For
is a complete If statement and it stands alone.
You want the "Else" to be a part of that If statement and it can't be. Put
the "Exit For" in the first line on the next line if you want to use "Else".
But the way the code is written, simply remove the "Else" and the "End If"
that goes with the first If statement.
Like this:
For Each c In Range("C:C")
If blnTest = True Then Exit For
If IsNumeric(c) Then
c.EntireColumn.Hidden
blnTest = True
End If
Next c
End Sub
HTH Otto

<goss9394@yahoo.com> wrote in message
news:1134949644.126533.310550@g44g2000cwa.googlegroups.com...
> Hi all
>
> Not sure why my code is failing
> Debug comesback highlighted at ELSE
>
> Error is Else without IF
>
> Thanks
> -goss
>
> Code :
>
> Option Explicit
>
> Sub TestValue()
> Dim c As Range
> Dim blnTest As Boolean
>
> blnTest = False
>
> For Each c In Range("C:C")
> If blnTest = True Then Exit For
> Else
> If IsNumeric(c) Then c.EntireColumn.Hidden
> blnTest = True
> End If
> End If
> Next c
> End Sub
>

Thanks Otto -

Now the c.EntireColumn.Hidden cameback as "Invalid use of the property"
I also tried c.EntireColumn.Visible = False
Cameback as End If without block If

Don't have much faith in this approach as the "Visible" did
not come up with intellisense though "Hidden" did.

Thanks
-goss

Revised code :

Option Explicit

Sub TestValue()
Dim c As Range
Dim blnTest As Boolean

blnTest = False

For Each c In Range("C:C")
If blnTest = True Then Exit For
If IsNumeric(c) Then c.EntireColumn.Visible = False
blnTest = True
End If
Next c
End Sub

Try fixing one more thing...

Please Login or Register  to view this content.

keep "c.EntireColumn.Visible (or Hidden)" on a separate line than "If IsNumeric(c) Then"

To perhaps explain the confusion:
There are two forms of an If... Then statement; a one-line statement and a
multiline statement.

The one line statement looks like this:
If condition Then statement1 [Else statement2]
The Else part is optional but if included must be on the same line; I often
use the line continuation character "_" to separate it just so I can read it
more easily, but it still counts as a single line.

The multiple line statement looks like:
If condition1 Then
statement1a
statement1b
statement1c...
[Else
statement2a
statement2b
statement2c...]
End If
Again, the Else part is optional. But what will not work is to combine the
two, as in this (error) example:
If condition1 Then statement1
Else
statement2a
statement 2b
End If

If you look at your original code, this was the error.

If blnTest = True Then Exit For ' SINGLE LINE format
Else ' MULTIPLE LINE format
If IsNumeric(c) Then c.EntireColumn.Hidden
blnTest = True
End If

'Else without If' means it found an Else alone on a line where it is not
expected (because the If ... Then above is complete; it is not part of a
multiline If statement)

For the next one:
If blnTest = True Then Exit For ' SINGLE LINE - this is OK here
If IsNumeric(c) Then c.EntireColumn.Visible = False 'SINGLE LINE
format again
blnTest = True
End If ' End If cannot be used here, since the IF was ended
already

'End If without block If' comes when the debugger hits an End If that does
not have a multiline If statement, i.e. a 'block If', above it.

Correct form (the other replies are also valid, but this is the way to write
a technically correct If... Then...Else to deal with these types of
conditions):

If blnTest = True Then
Exit For
Else
If IsNumeric(c) Then
c.EntireColumn.Hidden
blnTest = True
End If
End If

--
- K Dales


"Brassman" wrote:

>
> Try fixing one more thing...
>
> Code:
> --------------------
>
> Option Explicit
>
> Sub TestValue()
> Dim c As Range
> Dim blnTest As Boolean
>
> blnTest = False
>
> For Each c In Range("C:C")
> If blnTest = True Then Exit For
> If IsNumeric(c) Then
> c.EntireColumn.Visible = False
> blnTest = True
> End If
> Next c
> End Sub
>
> --------------------
>
> keep "c.EntireColumn.Visible (or Hidden)" on a separate line than "If
> IsNumeric(c) Then"
>
>
> --
> Brassman
> ------------------------------------------------------------------------
> Brassman's Profile: http://www.excelforum.com/member.php...o&userid=13290
> View this thread: http://www.excelforum.com/showthread...hreadid=494489
>
>

Thanks K Dales -
I have revised using your code

Here is new full code

Option Explicit

Sub TestValue()
Dim c As Range
Dim blnTest As Boolean

For Each c In Range("C:C")
If blnTest = True Then
Exit For
Else
If IsNumeric(c) Then
c.EntireColumn.Hidden
blnTest = True
End If
End If
Next c
End Sub

c.EntireColumn.Hidden still comes up "Invalid use of property"

Thanks
-goss

That is a separate issue; should be c.EntireColumn.Hidden = True
--
- K Dales


"goss9394@yahoo.com" wrote:

> Thanks K Dales -
> I have revised using your code
>
> Here is new full code
>
> Option Explicit
>
> Sub TestValue()
> Dim c As Range
> Dim blnTest As Boolean
>
> For Each c In Range("C:C")
> If blnTest = True Then
> Exit For
> Else
> If IsNumeric(c) Then
> c.EntireColumn.Hidden
> blnTest = True
> End If
> End If
> Next c
> End Sub
>
> c.EntireColumn.Hidden still comes up "Invalid use of property"
>
> Thanks
> -goss
>
>

Thanks -

Now comesback Next w/o For error.

A few other questions
When I type EntireColumn. Intellisense pops up a listbox of properties
and methods
When I type pastespecial I receive some xlxxxxxxxx options
But other times I have to know that I have to type Hidden = True or
False
Why is this?

Thanks
-goss

Revised code :

Option Explicit

Sub TestValue()
Dim c As Range
Dim blnTest As Boolean

blnTest = False

For Each c In Range("C:C")
If blnTest = True Then
Exit For
Else
If IsNumeric(c) Then
c.EntireColumn.Hidden = True
blnTest = True
End If
Next c
End Sub

First, you forgot an End If:
For Each c In Range("C:C")
If blnTest = True Then
Exit For
Else
If IsNumeric(c) Then
c.EntireColumn.Hidden = True
blnTest = True
End If
End If
Next c

Tip: by indenting (as you did) you can scan up and down the lines of code
and make sure every loop and multi-line statement has all the required parts
in it. I could see that the inner If had an End If, but the outer one only
had If.. and Else. Since it had not closed properly, when the debugger hit
the Next statement, it could not match it with the For. You cannot have a
loop that starts outside of an if statement and ends inside it.

The other question:
Intellisense will only give you options when there is a predefined list of
options (in the background, this is when the Excel object code contains an
enumerated list of constant values - like using the Enum statement). If the
options are simply common constants like true or false or numerical values
you can choose freely, you won't get any help of this sort.

--
- K Dales


"goss9394@yahoo.com" wrote:

> Thanks -
>
> Now comesback Next w/o For error.
>
> A few other questions
> When I type EntireColumn. Intellisense pops up a listbox of properties
> and methods
> When I type pastespecial I receive some xlxxxxxxxx options
> But other times I have to know that I have to type Hidden = True or
> False
> Why is this?
>
> Thanks
> -goss
>
> Revised code :
>
> Option Explicit
>
> Sub TestValue()
> Dim c As Range
> Dim blnTest As Boolean
>
> blnTest = False
>
> For Each c In Range("C:C")
> If blnTest = True Then
> Exit For
> Else
> If IsNumeric(c) Then
> c.EntireColumn.Hidden = True
> blnTest = True
> End If
> Next c
> End Sub
>
>

K Dales wrote:
> First, you forgot an End If:
> For Each c In Range("C:C")
> If blnTest = True Then
> Exit For
> Else
> If IsNumeric(c) Then
> c.EntireColumn.Hidden = True
> blnTest = True
> End If
> End If
> Next c
>
> Tip: by indenting (as you did) you can scan up and down the lines of code
> and make sure every loop and multi-line statement has all the required parts
> in it. I could see that the inner If had an End If, but the outer one only
> had If.. and Else. Since it had not closed properly, when the debugger hit
> the Next statement, it could not match it with the For. You cannot have a
> loop that starts outside of an if statement and ends inside it.
>
> The other question:
> Intellisense will only give you options when there is a predefined list of
> options (in the background, this is when the Excel object code contains an
> enumerated list of constant values - like using the Enum statement). If the
> options are simply common constants like true or false or numerical values
> you can choose freely, you won't get any help of this sort.
>
> --
> - K Dales
>
>
> "goss9394@yahoo.com" wrote:
>
> > Thanks -
> >
> > Now comesback Next w/o For error.
> >
> > A few other questions
> > When I type EntireColumn. Intellisense pops up a listbox of properties
> > and methods
> > When I type pastespecial I receive some xlxxxxxxxx options
> > But other times I have to know that I have to type Hidden = True or
> > False
> > Why is this?
> >
> > Thanks
> > -goss
> >
> > Revised code :
> >
> > Option Explicit
> >
> > Sub TestValue()
> > Dim c As Range
> > Dim blnTest As Boolean
> >
> > blnTest = False
> >
> > For Each c In Range("C:C")
> > If blnTest = True Then
> > Exit For
> > Else
> > If IsNumeric(c) Then
> > c.EntireColumn.Hidden = True
> > blnTest = True
> > End If
> > Next c
> > End Sub
> >
> >

Thanks
I had the second End If originally, but removed it based on Otto's
reply
But at that time the code was written

If blnTest = True Then Exit For
(Perhaps this synax terminates and the End If is not necessary?)

Instead of

If blnTest = True Then
Exit For
Else
If IsNumeric.......

Thanks
-goss