A question relating to "for each cell_in_loop in range"
 

I realize this is a somewhat more advance question while at the
same time somewhat simple. At the same time my problem has to
do with a rather bizarre.. well, let me describe the situation.

Given
ExitLine = 2000
For each cell_in_loop In Range("H2:H2000")
If (cell_in_loop.Offset(0, -7).Value = "EOList") Then
' ExitLine = ' ?? <------- This is where the problem arises
Exit For
End If
: Lengthy list of instructions
Next
MsgBox "Exited after line " & ExitLine

As you can see, this is, quite simply put, a straight forward,
"for...next" loop with no real "gotcha's" beyond a more-or-less
straight forward "get out early" clause which is also quite
straight forward.

I can know, with a fair degree of certainty that if the "loop
runs to the end" I have 2000 (or more) lines of data. Here's
where I've run into the problem. I haven't been able to find
a way to find out where I leave the loop, when I leave the
loop, early. I know there must be a way and I'm sure it's
quite simple, but for the life of me it's escaping me.

I've tried toying around with the internals of the "offset"
using "application.row()" but the compiler complains and
when I can get it to a run-able state it blows up as soon
as it hits that line.

I've tried counters but that got too convoluted to be
cleanly implemented (since the columns might have to
be sorted from time-to-time) and "EOList" may move to
a different place. (And the code was *butt crack ugly"
and too hard to hard to read as well. The understanding
or comprehension required a brain-damaged chimp with a
Ph.D in some obscure language no one has spoken for ten
million years. I'd like to leave code that the next guy
could "fix," if he had too, without boiling me in VR oil
each time he had to do a "fix.")

I tried some C++ routines but it was too easy to get lost
between the "external calls" to be certain I was really
certain that what I was getting was a correct value. (I
mean it "looked right" but it "looked wrong" too.)

So, I'll entertain all suggestions on how I can find out that
when I find "EOList" that the last line of usable data was:
"Row() - 1?"

Looking forward to good ideas.


David

(Newsgroups have *never* failed me in the past and I the idea
of them doing so now is "inconceivable." I can't even picture
the idea of them doing so now. - With slight credit to "The
Brute" in "The Princess Bride")

A question relating to "for each cell_in_loop in range"
 

ExitLine = 2000
For each cell_in_loop In Range("H2:H2000")
If (cell_in_loop.Offset(0, -7).Value = "EOList") Then
ExitLine = Cells_In_Loop.Row - 1
Exit For
End If
: Lengthy list of instructions
Next
MsgBox "Exited after line " & ExitLine

would be my guess.

--
Regards,
Tom Ogilvy


"David F. Schrader" wrote in message
...
I realize this is a somewhat more advance question while at the
same time somewhat simple. At the same time my problem has to
do with a rather bizarre.. well, let me describe the situation.

Given
ExitLine = 2000
For each cell_in_loop In Range("H2:H2000")
If (cell_in_loop.Offset(0, -7).Value = "EOList") Then
' ExitLine = ' ?? <------- This is where the problem arises
Exit For
End If
: Lengthy list of instructions
Next
MsgBox "Exited after line " & ExitLine

As you can see, this is, quite simply put, a straight forward,
"for...next" loop with no real "gotcha's" beyond a more-or-less
straight forward "get out early" clause which is also quite
straight forward.

I can know, with a fair degree of certainty that if the "loop
runs to the end" I have 2000 (or more) lines of data. Here's
where I've run into the problem. I haven't been able to find
a way to find out where I leave the loop, when I leave the
loop, early. I know there must be a way and I'm sure it's
quite simple, but for the life of me it's escaping me.

I've tried toying around with the internals of the "offset"
using "application.row()" but the compiler complains and
when I can get it to a run-able state it blows up as soon
as it hits that line.

I've tried counters but that got too convoluted to be
cleanly implemented (since the columns might have to
be sorted from time-to-time) and "EOList" may move to
a different place. (And the code was *butt crack ugly"
and too hard to hard to read as well. The understanding
or comprehension required a brain-damaged chimp with a
Ph.D in some obscure language no one has spoken for ten
million years. I'd like to leave code that the next guy
could "fix," if he had too, without boiling me in VR oil
each time he had to do a "fix.")

I tried some C++ routines but it was too easy to get lost
between the "external calls" to be certain I was really
certain that what I was getting was a correct value. (I
mean it "looked right" but it "looked wrong" too.)

So, I'll entertain all suggestions on how I can find out that
when I find "EOList" that the last line of usable data was:
"Row() - 1?"

Looking forward to good ideas.


David

(Newsgroups have *never* failed me in the past and I the idea
of them doing so now is "inconceivable." I can't even picture
the idea of them doing so now. - With slight credit to "The
Brute" in "The Princess Bride")

A question relating to "for each cell_in_loop in range"
 

Hi
This is one way:

Public Sub tester()
Dim MyRange As Range, cell_in_loop As Range
Dim FirstLine As Long, ExitLine As Long
Dim ExitLineFound As Boolean
Set MyRange = ActiveSheet.Range("H2:H2000")
FirstLine = MyRange.Row
ExitLineFound = False
For Each cell_in_loop In MyRange
If (cell_in_loop.Offset(0, -7).Value = "EOList") Then
ExitLine = cell_in_loop.Row - FirstLine + 1
ExitLineFound = True
Exit For
End If
Next cell_in_loop
If ExitLineFound Then
MsgBox "Exited at line " & ExitLine
Else
MsgBox "The text EOList was not found"
End If
Set MyRange = Nothing
End Sub

The Row property of a Range object gives its first row number (the
numbers you see on the Excel sheet down the left hand side)

A question relating to "for each cell_in_loop in range"
 

I was certain I answered this at the individual
level but just to be safe...

Paul this works as you indicated. It's a little
out of the way of what I wanted but I've
worked it into my code so that now I've
got what I need.

David

wrote in message
oups.com...
Hi
This is one way:

Public Sub tester()
Dim MyRange As Range, cell_in_loop As Range
Dim FirstLine As Long, ExitLine As Long
Dim ExitLineFound As Boolean
Set MyRange = ActiveSheet.Range("H2:H2000")
FirstLine = MyRange.Row
ExitLineFound = False
For Each cell_in_loop In MyRange
If (cell_in_loop.Offset(0, -7).Value = "EOList") Then
ExitLine = cell_in_loop.Row - FirstLine + 1
ExitLineFound = True
Exit For
End If
Next cell_in_loop
If ExitLineFound Then
MsgBox "Exited at line " & ExitLine
Else
MsgBox "The text EOList was not found"
End If
Set MyRange = Nothing
End Sub

The Row property of a Range object gives its first row number (the
numbers you see on the Excel sheet down the left hand side)