Next inside If

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
 
W

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
 
M

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
 
C

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

Rick Rothstein

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
 
R

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)


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
 
C

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

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
 
P

Patrick Molloy

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top