Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Several AND operators inside an IF... | Excel Programming | |||
I need to use more than 7 formulas inside each other | Excel Discussion (Misc queries) | |||
2 cells inside of one | New Users to Excel | |||
add inside borders | Excel Discussion (Misc queries) | |||
trying to email .xla inside/with .xls | Excel Discussion (Misc queries) |