Next inside If
 

I have something like

For Each cel in Sheets("Mate").Range(A1:A10000).Cells
If IsEmpty(cel.Value) then Next cel
If ......
.....
End If
Next cel

This fails with a "Next without For" message.

To make it work I have changed the code to

For Each cel in Sheets("Mate").Range(A1:A10000).Cells
If IsEmpty(cel.Value) then GoTo MyLabel
If ......
.....
End If
MyLabel:
Next cel


But I wonder if it can be done, without the GoTo statement?

Jan

Next inside If
 

If the cell contains something, allow the next line, otherwise end the
if:

Sub Macro1()
Dim cel As Range
For Each cel In Sheet1.Range("A1:A10000") '.Cells
If IsEmpty(cel.Value) = False Then 'Next cel
If 1 0 Then
MsgBox "!!!"
End If
End If
Next cel
End Sub

Next inside If
 

Thanks !

Jan

ward376 wrote:
If the cell contains something, allow the next line, otherwise end the
if:

Sub Macro1()
Dim cel As Range
For Each cel In Sheet1.Range("A1:A10000") '.Cells
If IsEmpty(cel.Value) = False Then 'Next cel
If 1 0 Then
MsgBox "!!!"
End If
End If
Next cel
End Sub

Next inside If
 

Maybe this

For Each cel In Sheets("Mate").Range("A1:A10000")
If Not IsEmpty(cel) Then
MsgBox cel.Value
End If
Next cel

Mike

"Jan Kronsell" wrote:

I have something like

For Each cel in Sheets("Mate").Range(A1:A10000).Cells
If IsEmpty(cel.Value) then Next cel
If ......
.....
End If
Next cel

This fails with a "Next without For" message.

To make it work I have changed the code to

For Each cel in Sheets("Mate").Range(A1:A10000).Cells
If IsEmpty(cel.Value) then GoTo MyLabel
If ......
.....
End If
MyLabel:
Next cel


But I wonder if it can be done, without the GoTo statement?

Jan


.

Next inside If
 

Personally, I think it is a very bad programming practice to do
anything at all with the index variable of a For loop. It leads to
messy and complicated code that is difficult to debug, maintain, and
enhance. You should touch the index variable.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Sat, 19 Dec 2009 16:39:25 +0100, "Jan Kronsell"
wrote:

I have something like

For Each cel in Sheets("Mate").Range(A1:A10000).Cells
If IsEmpty(cel.Value) then Next cel
If ......
.....
End If
Next cel

This fails with a "Next without For" message.

To make it work I have changed the code to

For Each cel in Sheets("Mate").Range(A1:A10000).Cells
If IsEmpty(cel.Value) then GoTo MyLabel
If ......
.....
End If
MyLabel:
Next cel


But I wonder if it can be done, without the GoTo statement?

Jan

Next inside If
 

Chip meant to say in his last line...

You should **not** touch the index variable.

--
Rick (MVP - Excel)


"Chip Pearson" wrote in message
...
Personally, I think it is a very bad programming practice to do
anything at all with the index variable of a For loop. It leads to
messy and complicated code that is difficult to debug, maintain, and
enhance. You should touch the index variable.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Sat, 19 Dec 2009 16:39:25 +0100, "Jan Kronsell"
wrote:

I have something like

For Each cel in Sheets("Mate").Range(A1:A10000).Cells
If IsEmpty(cel.Value) then Next cel
If ......
.....
End If
Next cel

This fails with a "Next without For" message.

To make it work I have changed the code to

For Each cel in Sheets("Mate").Range(A1:A10000).Cells
If IsEmpty(cel.Value) then GoTo MyLabel
If ......
.....
End If
MyLabel:
Next cel


But I wonder if it can be done, without the GoTo statement?

Jan

Next inside If
 

But in re-looking at the OP's code, I'm not sure why Chip offered this
advice (which, on its own face, is excellent advice) as it just doesn't seem
to be applicable to the OP's message.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Chip meant to say in his last line...

You should **not** touch the index variable.

--
Rick (MVP - Excel)


"Chip Pearson" wrote in message
...
Personally, I think it is a very bad programming practice to do
anything at all with the index variable of a For loop. It leads to
messy and complicated code that is difficult to debug, maintain, and
enhance. You should touch the index variable.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Sat, 19 Dec 2009 16:39:25 +0100, "Jan Kronsell"
wrote:

I have something like

For Each cel in Sheets("Mate").Range(A1:A10000).Cells
If IsEmpty(cel.Value) then Next cel
If ......
.....
End If
Next cel

This fails with a "Next without For" message.

To make it work I have changed the code to

For Each cel in Sheets("Mate").Range(A1:A10000).Cells
If IsEmpty(cel.Value) then GoTo MyLabel
If ......
.....
End If
MyLabel:
Next cel


But I wonder if it can be done, without the GoTo statement?

Jan

Next inside If
 

Rick,

You're right, I omitted a "not" in the last sentence. I offered this
observation because in the original post, the user had a Next within
the loop in an If statement. While that isn't really changing the
index variable, it is bad code and I just decided to expand on the
topic.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Sat, 19 Dec 2009 15:37:11 -0500, "Rick Rothstein"
wrote:

But in re-looking at the OP's code, I'm not sure why Chip offered this
advice (which, on its own face, is excellent advice) as it just doesn't seem
to be applicable to the OP's message.

Next inside If
 

what is the point of
If 1 0 Then
as it will always be true?

Sub Macro1()
Dim cell As Range
For Each cell In Sheet1.Range("A1:A10000") .Cells
If NOT IsEmpty(cell.Value) Then
MsgBox cell.value,,cell.address(false,false)
End If
Next
End Sub



"ward376" wrote in message
...
If the cell contains something, allow the next line, otherwise end the
if:

Sub Macro1()
Dim cel As Range
For Each cel In Sheet1.Range("A1:A10000") '.Cells
If IsEmpty(cel.Value) = False Then 'Next cel
If 1 0 Then
MsgBox "!!!"
End If
End If
Next cel
End Sub

Next inside If
 

the op's original code wouldn't have compiled since he had two 'next'
statements.

"Chip Pearson" wrote in message
...
Rick,

You're right, I omitted a "not" in the last sentence. I offered this
observation because in the original post, the user had a Next within
the loop in an If statement. While that isn't really changing the
index variable, it is bad code and I just decided to expand on the
topic.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Sat, 19 Dec 2009 15:37:11 -0500, "Rick Rothstein"
wrote:

But in re-looking at the OP's code, I'm not sure why Chip offered this
advice (which, on its own face, is excellent advice) as it just doesn't
seem
to be applicable to the OP's message.

Next inside If
 

On Dec 20, 5:55*am, "Patrick Molloy"
wrote:
what is the point of
* * * * * * If 1 0 Then
as it will always be true?

Just an example preserving the structure the OP had posted.

Thanks! Cliff Edwards