Next inside If

  • Thread starter Thread starter Jan Kronsell
  • Start date Start date
J

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

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

Mike
 
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]
 
Chip meant to say in his last line...

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

--
Rick (MVP - Excel)


Chip Pearson said:
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
Click to expand...
Click to expand...
 
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 said:
Chip meant to say in his last line...

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

--
Rick (MVP - Excel)


Chip Pearson said:
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
Click to expand...
Click to expand...
Click to expand...
 
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]
 
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
 
the op's original code wouldn't have compiled since he had two 'next'
statements.

Chip Pearson said:
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.
Click to expand...
Click to expand...
 
Back
Top