Re: A question relating to "for each cell_in_loop in range"

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Tom Ogilvy (twogilvy_at_msn.com)
Date: 02/23/05 

Date: Wed, 23 Feb 2005 09:17:19 -0500

   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" <schrader@acns.fsu.edu> wrote in message
news:uBsEiEbGFHA.3244@TK2MSFTNGP15.phx.gbl...
> 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 **** 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")
>
>
>
>
Quantcast