Next inside If

[Jan Kronsell]

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

 

[ward376]

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

 

[Jan Kronsell]

Thanks !

Jan

 

[Mike H]

Maybe this

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

Mike

 

[Chip Pearson]

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]

 

[Rick Rothstein]

Chip meant to say in his last line...

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

--
Rick (MVP - Excel)

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]

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

 

[Rick Rothstein]

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)

Chip meant to say in his last line...

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

--
Rick (MVP - Excel)

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]

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

 

[Chip Pearson]

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]

 

[Patrick Molloy]

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

 

[Patrick Molloy]

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

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]

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.

 

[ward376]

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