If Then Else with Exit For

[[email protected]]

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

[Otto Moehrbach]

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

<[email protected]> wrote in message
news:[email protected]...
> 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
>

[[email protected]]

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

[Brassman]

Try fixing one more thing...

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"

[K Dales]

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
>
>

[[email protected]]

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