For Each Loop
Bill
take care a compile error: "Next without For" located
at "Next rngMycell" line.
---------------------------------------
For Each rngMyCell in Range("A1:A12")
If Len(rngMyCell.Value) <> 8 Then
Next rngMycell
End If
... code here
Next
---------------------------------------
The purpose of this "If...Then..." statement is to check String
Length, if the condition is not meet, then move on to the next row.
Thanks for your help,
Greg McIntire
The reason you're getting your error is because the compiler sees Next
rngMyCell and associates that with the for each...the compiler doesn't
recognize that next as condition and if it's not met then use the bottom
one.
However, a small revision of strucutre will get you what you want:
For Each rngMyCell in Range("A1:A12")
If Len(rngMyCell.Value) = 8 Then
... code here
endif
Next rngMyCell
---------------------------------------
Notice here that placing the if statement directly inside the loop if the
conditions are not met, it does nothing...but loop to the next cell.
HTH,
Greg McIntire
"Bill" <billg...@hotmail.com> wrote in message
news:#30P3zemBHA.980@tkmsftngp03...
Harald Staff
Don't do the "next" stuff in the middle. For -next takes care of the moving:
For Each rngMyCell in Range("A1:A12")
If Len(rngMyCell.Value) = 8 Then ', if the condition IS met
'... code here
End if
Next
HTH. Best wishes Harald
"Bill" <billg...@hotmail.com> wrote in message
news:#30P3zemBHA.980@tkmsftngp03...
J.E. McGimpsey
For must have exactly one Next at the same "level":
For Each rngMyCell in range("A1:A12")
If Len(rngMyCell.Value) = 8 Then
...code here
End If
Next rngMyCell
In article <#30P3zemBHA.980@tkmsftngp03>, Bill <billg...@hotmail.com>
wrote:
--
Email address ROT13 encoded. Decode for real address.
Nico Sterk
You make a serious mistake in your code. The If Then Else clause should be
decently nested within the For Next clause, this means that IfThenElse
should be totally WITHIN ForNext.
Try,
'------------------------------------------------------------------
Sub test()
Dim rngMycell As Range
For Each rngMycell In Range("A1:A12")
If Len(rngMycell.Value) <> 8 Then
DoNothing
Else
DoMyThing
End If
Next rngMycell
End Sub
Sub DoMyThing()
End Sub
Sub DoNothing()
End Sub
'---------------------------------------------------------------------
HTH
--
Nico Sterk
Venuslaan 71
5632 HA Eindhoven
Netherlands
+31 40 242 9317
+31 6 29 10 70 15
n.s...@chello.nl
http://members.tripodnet.nl/excelsoftware
"Bill" <billg...@hotmail.com> wrote in message
news:#30P3zemBHA.980@tkmsftngp03...
Tim Zych
evaluation for some other reason:
For Each rngMyCell In Range("A1:A12")
If Len(rngMyCell.Value) <> 8 Then
'Do nothing
Else
'Code
End If
Next
"Bill" <billg...@hotmail.com> wrote in message
news:#30P3zemBHA.980@tkmsftngp03...
Nico Sterk
Sub test()
Dim rngMycell As Range
For Each rngMycell In Range("A1:A12")
If Len(rngMycell.Value) = 8 Then
DoMyThing
End If
Next rngMycell
End Sub
Sub DoMyThing()
End Sub
--
Nico Sterk
Venuslaan 71
5632 HA Eindhoven
Netherlands
+31 40 242 9317
+31 6 29 10 70 15
n.s...@chello.nl
http://members.tripodnet.nl/excelsoftware
"Bill" <billg...@hotmail.com> wrote in message
news:#30P3zemBHA.980@tkmsftngp03...
Bill
You guys are really great.
Thanks for your help!!!
"J.E. McGimpsey" <ar...@zptvzcfrl.pbz> wrote in message
news:100120020856155316%ar...@zptvzcfrl.pbz...
Bill
Thank you and Tim's help too!!!
"Nico Sterk" <n.s...@chello.nl> wrote in message
news:#$rOzCfmBHA.1060@tkmsftngp04...
Chip Pearson
> You need to nest your control structures - first in, last out, and each
> For must have exactly one Next at the same "level":
Actually, I read somewhere of a way to do what perhaps the original poster
wanted to do -- e.g., skip an entry in a For Next loop. In other words, a
For/Next loop will process (in ordinal numbers) 1,2,3,4.... But suppose you
really did want, within the body of the loop, to alter this sequence to
1,2,4,5....skipping #3. There is actually a way to do that, but it is a bitch
in VB/VBA because the Skip method is marked as 'out-only' and VB/VBA doesn't
support those type of methods. You don't see the Skip method in VB but it is
there deep under the covers. (C++ will support this sort of thing, because it
doesn't put restrictions on the types that VB does.)
The solution used a user-defined TypeLib to override the method of the
Collection. I'll try to dig up the code. It won't help the original user (and
probably won't help you Mac users), but it was pretty slick. (Most likely, this
came from Matt Curland's "Advanced VB6 Programming" book.)
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com
"J.E. McGimpsey" <ar...@zptvzcfrl.pbz> wrote in message
news:100120020856155316%ar...@zptvzcfrl.pbz...
J.E. McGimpsey
I don't think in this case it would help the OP, since the Exit/Skip
was on the basis of a single test- which, practically, means at most
saving one branch instruction to the loop branch.
While I haven't been delving into it yet, I suspect the *nix engine in
Mac OSX will make Mac system call development easier.
In article <#nhZ4UfmBHA.1600@tkmsftngp07>, Chip Pearson
<ch...@cpearson.com> wrote:
> > You need to nest your control structures - first in, last out, and each
> > For must have exactly one Next at the same "level":
>
> Actually, I read somewhere of a way to do what perhaps the original poster
> wanted to do -- e.g., skip an entry in a For Next loop. In other words, a
> For/Next loop will process (in ordinal numbers) 1,2,3,4.... But suppose you
> really did want, within the body of the loop, to alter this sequence to
> 1,2,4,5....skipping #3. There is actually a way to do that, but it is a bitch
> in VB/VBA because the Skip method is marked as 'out-only' and VB/VBA doesn't
> support those type of methods. You don't see the Skip method in VB but it is
> there deep under the covers. (C++ will support this sort of thing, because it
> doesn't put restrictions on the types that VB does.)
>
> The solution used a user-defined TypeLib to override the method of the
> Collection. I'll try to dig up the code. It won't help the original user
> (and
> probably won't help you Mac users), but it was pretty slick. (Most likely,
> this
> came from Matt Curland's "Advanced VB6 Programming" book.)
>
>
>
>
--
Harald Staff
rewritten:
Sub Skip()
Dim i As Integer
For i = 1 To 6
If i = 3 Then i = 4
Debug.Print i
Next
End Sub
Best wishes Harald
"Chip Pearson" <ch...@cpearson.com> wrote in message
news:#nhZ4UfmBHA.1600@tkmsftngp07...
Chip Pearson
> It's possible. I've done it like this in big procedures, blushed and
> rewritten:
Upon rereading my previous reply, I realize I made a mistake. I wrote "For
Next" when I really meant "For Each". Standard "For Next" loops are pretty
simple, and as you pointed out, you can change the index variable as you want.
It is bad code, but it is easy to do.
What I *meant* to write was that there exists a way to change the pointer in a
"For Each" loop. E.g.,
Dim R As Range
For Each R In Range("A1:A10")
If R.Row = 5 Then
Skip R ' << obviously, does not work, but there is a way to do it
End If
Debug.Print R.Address
Next R
A VB For/Each loop actually goes through (and I'll admit I'm at the very edge of
my competence here, perhaps Rob will bail me out) the _NewEnum method. This is
hidden, but accessible in VB/VBA. _NewEnum give you a pointer to an interface
(whose name I forget) that includes a method called, I think, Skip. The Skip
method isn't available in VB because of the way it is defined (VB doesn't
support that particular flavor of declaration). The code that I can't find at
the moment used a custom TypeLib to change the declaration of Skip, and would
allow you to call the method. (Help me Rob, I making this up as I go along. But
it does sound good, doesn't it?)
I'll try to dig up the code. I've got it cornered on a particular machine, but
still can't find the actual source.
And yes, you should blush if you change the index variable in a 'standard' For
Next loop. It makes debugging terribly difficult. Don't do it. (You're a
'Simpson's' fan -- remember the Sex Education film with Troy McClure: "Now that
you know how to do it, don't!")
Of course, the same can be said for the Skip method of _NewEnum. I brought it
up not as an example of 'good code' but simply because programmers like us think
this sort of thing is 'cool'.
Sorry for any confusion. I wrote "For Next" where I really meant "For Each".
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com
"Harald Staff" <harald...@eunet.no> wrote in message
news:eAjOIEhmBHA.1756@tkmsftngp07...
Nico Sterk
In fact it is a little obscure to me what the very point of this discussion
really is. In other words, waht is the advantage of explicitly calling a
skip method. Of course you can write an empty Sub like
Sub Skip()
End Sub
and embed it in a For Next loop, but there is no good reason for it as far
as I can image. What I can image is that in case of a loop like
For i = 1 to 10
Next i
you never want i to reach a value 5 for example, but directly from 4 to 6,
even withour a test within the loop to see if i=5. But why. Only in very
time critical applications where performance is an issue, omitting a test
(If Then Else clause) in the loop will save you time, and a second reason
for doing so might be just the readability of the code, especially in case
testing for the value of i implies spaghetti-like structures. In other
words, I can think of good reasons to avoid tasting within the loop but
having i run only through a predefined non-contiguous range of values.
Another note about performance: if this is the main issue, why use VBA?
Ohter points of view warmly invited,
--
Nico Sterk
Venuslaan 71
5632 HA Eindhoven
Netherlands
+31 40 242 9317
+31 6 29 10 70 15
n.s...@chello.nl
http://members.tripodnet.nl/excelsoftware
"Chip Pearson" <ch...@cpearson.com> wrote in message
news:eFVI8XhmBHA.1944@tkmsftngp07...
Harald Staff
> Upon rereading my previous reply, I realize I made a mistake.
Me too - I started on a " With object collections, however," part, realized
it was leading nowhere, deleted that and posted the rest without asking if
it made sense on its own.
Whatever, our conclusions on structure are identical.
Best wishes Harald
-Off to clear the immediate windows <g>