Re: One more loop question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Patti (MrsT_at_SPAM_NOT.com)
Date: 06/06/04 

Date: Sun, 6 Jun 2004 01:14:30 -0500

Bob, that is a beautiful thing! You're a gem - thanks again for all your
help.

Patti

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uTrj7FzSEHA.3608@TK2MSFTNGP11.phx.gbl...
> Ok Patti, I understand now. The seem in g contradictions make sense now
> trhat I see the data.
>
> Hopefully, this does it. I hade to get rid of the Do Loop.
>
> Private Sub testloop()
>
> Dim LstRow As Long
> Dim i As Long
> Dim AgtName As String
> Dim oCurrent As Range
> Dim FoundFlag As Boolean
>
> Columns("P:P").ClearContents
> LstRow = Range("a" & Rows.Count).End(xlUp).Row
> AgtName = Range("A1").Value
> Set oCurrent = Range("A1")
> FoundFlag = False
>
> For i = 1 To LstRow
>
> If Cells(i, "A") <> AgtName Then
> AgtName = Cells(i, "A").Value
> Set oCurrent = Cells(i, "A")
> FoundFlag = False
> End If
>
> If FoundFlag = False And Cells(i, "A").Value = AgtName Then
> Select Case LCase(Cells(i, 12).Value)
> Case ""
> oCurrent.Offset(0, 15).Value = "I found a Null cell "
> FoundFlag = True
> Case "this"
> oCurrent.Offset(0, 15).Value = "I found This!"
> FoundFlag = True
> Case "that"
> oCurrent.Offset(0, 15).Value = "I found That!"
> FoundFlag = True
> Case Else
> End Select
> End If
>
> Next i
>
> End Sub
>
> --
>
> HTH
>
> Bob Phillips
> ... looking out across Poole Harbour to the Purbecks
> (remove nothere from the email address if mailing direct)
>
> "Patti" <MrsT@SPAM_NOT.com> wrote in message
> news:%23Kq9pTySEHA.1472@TK2MSFTNGP09.phx.gbl...
> > I appreciate you hanging in there with me...
> >
> > 1) First, look for first instance of a name in column A
> >
> > 2) Within all rows for that name, look for first instance of either a
Null
> > OR a valid code in column L. To simplify, lets assume my only valid
codes
> > are "THIS" & "THAT". Again, if I find a Null, I don't need to look any
> > further for that name.
> >
> > COLUMN A COLUMN L
> > Jones Cat
> > Jones THIS '< I need to
capture
> > this for Jones
> > Smith Dog
> > Smith Rat
> > Smith Pig
> > Smith THAT ' < I need to
capture
> > this for Smith
> > Smith Rabbit
> > Thomas '< I don't
need
> to
> > look any further for Thomas
> > Thomas
> > Thomas
> >
> > If I find a valid code for a particular name, I need to plunk a comment
in
> > Column P of the row where the first instance of that name appeared, then
> go
> > on to the next name. The output would then look like
> >
> > COLUMN A COLUMN L COLUMN P
> > Jones Cat
"I
> > found THIS"
> > Jones THIS
> > Smith Dog
"I
> > found THAT"
> > Smith Rat
> > Smith Pig
> > Smith THAT
> > Smith Rabbit
> > Thomas
> > Thomas
> > Thomas
> >
> > I won't be able to check back until morning...
> >
> > You're awesome Bob!
> >
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:%23RhFlAySEHA.1508@TK2MSFTNGP11.phx.gbl...
> > > "Patti" <MrsT@SPAM_NOT.com> wrote in message
> > > news:ugf1lcxSEHA.3720@TK2MSFTNGP10.phx.gbl...
> > > > > But are we talking about the first row with a valid code, or just
> the
> > > > first
> > > > > row for the name?
> > > >
> > > > Always to the first row for the name... I'm moving the data I need
to
> > the
> > > > first row for each name. In the end, duplicate rows will be
deleted,
> so
> > > all
> > > > info needs to be on the first row for that name.
> > > >
> > > > I'm just not explaining myself well. Should be once I find a valid
> > code
> > > OR
> > > > a NULL. I real life, when I find a NULL, I just want to set
FoundFlag
> > to
> > > > True so that it will END SELECT and go on to the next AgtName. In
the
> > > test
> > > > loop, I am writing a comment in column P when NULL is found purely
so
> > that
> > > I
> > > > can see that it is working.
> > >
> > > We don't seem to be connecting. Those two statements above seem to be
at
> > > odds to me. The first says pick up the first instance of a name, the
> > second
> > > says pivck up the firtgs instance of a valid code. They may not be the
> > same
> > > row. Map out some (good) exsample data, and expected results.
> > >
> > > > That much I know ; ) I would have thought it'd be adaptable
> though.
> > > > I know you provided alternate code that doesn't skip a row, but as I
> > said,
> > > > it only find a valid code OR Null ( my "FoundFlag") if appears in
the
> > same
> > > > row as the first instance of the name. This won't always be the
> case...
> > >
> > > If we can get to the stage when I understand what the requirement is I
> > will
> > > try and do that.
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: One more loop question
    ... Dim LstRow As Long ... > 2) Within all rows for that name, look for first instance of either a Null ... > OR a valid code in column L. To simplify, lets assume my only valid codes ... > look any further for Thomas ...
    (microsoft.public.excel.programming)
  • Re: Saving a file with time and date at a set time period
    ... Sorry Bob I was having a hard time getting my head around the OnTime thingy ... Sub CheckBox1_Click ... Dim ws As Worksheet.Name ... I have a choice of 2 macro's to choose from 1- gsnu and 2- ...
    (microsoft.public.excel.programming)
  • Re: VBA for converting data into various CASES.
    ... Bob, I had posted another question in this forum under the heading 'Copy ... > Private Sub ChangeCase() ... > Dim cell As Range ... > For Each cell In Selection ...
    (microsoft.public.excel.worksheet.functions)
  • Re: How do I solve this?
    ... Bob Phillips ... > itself, if I enter a number from 1 to 12 in a sheet, the others do not ... >> Private Sub Workbook_SheetChange(ByVal Sh As Object, ... >> Dim oSheet As Worksheet ...
    (microsoft.public.excel.programming)
  • FOX, Global developing "Bob & Doug McKenzie" cartoon
    ... Fox joins animated 'Bob & Doug' ... Series reunites Rick Moranis and Dave Thomas ...
    (rec.arts.tv)