Can someone please explain to me why the following code is skipping each row
in which the *first* instance of a new name appears in column A? In other
words, the Case Statement starts evaluating on the 2nd occurrence of the
name.

(thanks Bob, for all your help so far...)

TIA,

Patti


Option Explicit
Private Sub testloop()

Dim LstRow As Long
Dim i As Long
Dim AgtName As String
Dim oCurrent As Range
Dim FoundFlag As Boolean

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
Do While FoundFlag = False
Select Case Cells(i, 12)
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
FoundFlag = True
End Select
Loop
' message box added to confirm which row is being
evaluated
MsgBox "Currently evaluating row " & i & " for agent
" & AgtName
Else
AgtName = Cells(i, "A").Value
Set oCurrent = Cells(i, "A")
FoundFlag = False
End If
Next i

End Sub

Patti,

You skip a row on change of name rather than process it. I've tested it
before the Do Loop, and removed the Else.

Also, you don't need the oCurrent variable, you can use the index counter

Public Sub testloop()

Dim LstRow As Long
Dim i As Long
Dim AgtName As String
Dim FoundFlag As Boolean

LstRow = Range("A" & Rows.Count).End(xlUp).Row
AgtName = Range("A1").Value
FoundFlag = False

For i = 1 To LstRow

With Cells(i, "A")
If .Value < AgtName Then
AgtName = .Value
FoundFlag = False
End If
Do While FoundFlag = False
Select Case .Cells(1, 12).Value
Case ""
.Offset(0, 15).Value = "I found a Null cell"
FoundFlag = True
Case "This"
.Offset(0, 15).Value = "I found This!"
FoundFlag = True
Case "That"
.Offset(0, 15).Value = "I found That!"
FoundFlag = True
Case Else
FoundFlag = True
End Select
Loop
' message box added to confirm which row is being evaluated
MsgBox "Currently evaluating row " & i & " for agent " & AgtName
End With
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" wrote in message
...
Can someone please explain to me why the following code is skipping each
row
in which the *first* instance of a new name appears in column A? In
other
words, the Case Statement starts evaluating on the 2nd occurrence of the
name.

(thanks Bob, for all your help so far...)

TIA,

Patti


Option Explicit
Private Sub testloop()

Dim LstRow As Long
Dim i As Long
Dim AgtName As String
Dim oCurrent As Range
Dim FoundFlag As Boolean

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
Do While FoundFlag = False
Select Case Cells(i, 12)
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
FoundFlag = True
End Select
Loop
' message box added to confirm which row is being
evaluated
MsgBox "Currently evaluating row " & i & " for
agent
" & AgtName
Else
AgtName = Cells(i, "A").Value
Set oCurrent = Cells(i, "A")
FoundFlag = False
End If
Next i

End Sub

Hi Bob,

You are right that this now evaluates every row...but now it won't find the
flag for each name unless it appears in the same row as the first instance
of the name.

So if I have in column A: Column L:

Smith blah
Smith This '( my flag)

I want column P of the *first* row where Smith occurs to say "I found This!"
The use of the oCurrent variable was actually a tip I got from you, to
capture the row where I found the first instance of a new name. And of
course it works great.

(sorry if I broke any rules of netiquette by staring a new thread...nearly a
week has passed since I last posted & I thought it unlikely that the last
would be revisited).

Anyway, I just don't understand the "why" behind it skipping a row on the
change of name. As long as I'm stuck with this problem, I am trying to
make it a good learning experience...

I really appreciate all the help you given me.

Patti






"Bob Phillips" wrote in message
...
Patti,

You skip a row on change of name rather than process it. I've tested it
before the Do Loop, and removed the Else.

Also, you don't need the oCurrent variable, you can use the index counter

Public Sub testloop()

Dim LstRow As Long
Dim i As Long
Dim AgtName As String
Dim FoundFlag As Boolean

LstRow = Range("A" & Rows.Count).End(xlUp).Row
AgtName = Range("A1").Value
FoundFlag = False

For i = 1 To LstRow

With Cells(i, "A")
If .Value < AgtName Then
AgtName = .Value
FoundFlag = False
End If
Do While FoundFlag = False
Select Case .Cells(1, 12).Value
Case ""
.Offset(0, 15).Value = "I found a Null cell"
FoundFlag = True
Case "This"
.Offset(0, 15).Value = "I found This!"
FoundFlag = True
Case "That"
.Offset(0, 15).Value = "I found That!"
FoundFlag = True
Case Else
FoundFlag = True
End Select
Loop
' message box added to confirm which row is being evaluated
MsgBox "Currently evaluating row " & i & " for agent " &
AgtName
End With
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" wrote in message
...
Can someone please explain to me why the following code is skipping each
row
in which the *first* instance of a new name appears in column A? In
other
words, the Case Statement starts evaluating on the 2nd occurrence of the
name.

(thanks Bob, for all your help so far...)

TIA,

Patti


Option Explicit
Private Sub testloop()

Dim LstRow As Long
Dim i As Long
Dim AgtName As String
Dim oCurrent As Range
Dim FoundFlag As Boolean

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
Do While FoundFlag = False
Select Case Cells(i, 12)
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
FoundFlag = True
End Select
Loop
' message box added to confirm which row is being
evaluated
MsgBox "Currently evaluating row " & i & " for
agent
" & AgtName
Else
AgtName = Cells(i, "A").Value
Set oCurrent = Cells(i, "A")
FoundFlag = False
End If
Next i

End Sub

"Patti" wrote in message
...
Hi Bob,

You are right that this now evaluates every row...but now it won't find
the
flag for each name unless it appears in the same row as the first instance
of the name.

So if I have in column A: Column L:

Smith blah
Smith This '( my flag)


I thought I might have messed this bit up when I moved the Found flag, but I
couldn't remember the exact requirment from your previous post. Explain what
you want again. FRom this post I read it that you want to highlight the
first row with another value in column L, but your case statement evaluates
empty as a value. If the offset value is empty would this count as found, or
should it just be This, That, etc.


I want column P of the *first* row where Smith occurs to say "I found
This!"

In my testing, this happened, as all of mine had THis, That or were empty
which i9s what the Case statement tells it to.

The use of the oCurrent variable was actually a tip I got from you, to
capture the row where I found the first instance of a new name. And of
course it works great.


Now stop that, throwing what I said back at me <VBG. If I recall, you
would use that to save the cell and then use that cell object thereafcter,
but the way you have implemented the code is to save all of the values
separately (i as the row, AgtName as the agent name), so saving the cell as
well is superfluous, not wrong, just overkill.

(sorry if I broke any rules of netiquette by staring a new thread...nearly
a
week has passed since I last posted & I thought it unlikely that the last
would be revisited).


Who cares. I personally think there is a lot of fluff about netiquette.
There are some practices that make life simpler, but they are not rules.

Anyway, I just don't understand the "why" behind it skipping a row on the
change of name. As long as I'm stuck with this problem, I am trying to
make it a good learning experience...


The why is because you had an If Else Endif. The If evaialuated the current
row, whereas the Else reset Agtname. But each case looped to the next item,
so the Else did not also go through the cell evaluation, whereas it does in
my example because it is an indecent if, before the Case evaluation.

Bob,

I'm sure this problem is nowhere near as confusing as my explanation my make
it seem...

For each name in column A, I need to look down a list for various codes in
column L. Once I find a valid code, I want to quit looking, and enter what
I found (via the case statement) in column P of the first row in which that
name appeared. It has to be in the row with the _first_ instance of the
name because once I get this Sub to work, I am going to delete rows so that
each name appears only once (keeping the row where the name first appears).

In my testing, this happened, as all of mine had THis, That or were
empty
which i9s what the Case statement tells it to.


I probably should have mentioned that not all cells will have a valid code
in them...I'm picking through the garbage that I don't care about.

The reason for looking for the Null in the first Case statement is because I
know that if I find a Null, the rest of the cells in column L for that
particular name will be Null too... so I just want to quit looking, set my
FoundFlag to True and move on to the next name.

The why is because you had an If Else Endif. The If evaialuated the
current
row, whereas the Else reset Agtname. But each case looped to the next
item,
so the Else did not also go through the cell evaluation, whereas it does
in
my example because it is an indecent if, before the Case evaluation.

Oh, I get it now... but how can I reset the Agtname _and_ have it evaluated
in the first instance? I tried to use "offset" to set it to the name on
the _next_ row, but it still skipped the first instance of a new name.

If possible, I'd really like to stick close to the code I've got. I'm sure
that there is always going to be a technically "better" method, but I really
need to learn to do this myself, & it's hard to get a handle on when I keep
"starting over".

Thanks!

Patti


"Bob Phillips" wrote in message
...
"Patti" wrote in message
...
Hi Bob,

You are right that this now evaluates every row...but now it won't find
the
flag for each name unless it appears in the same row as the first
instance
of the name.

So if I have in column A: Column L:

Smith blah
Smith This '( my flag)


I thought I might have messed this bit up when I moved the Found flag, but
I
couldn't remember the exact requirment from your previous post. Explain
what
you want again. FRom this post I read it that you want to highlight the
first row with another value in column L, but your case statement
evaluates
empty as a value. If the offset value is empty would this count as found,
or
should it just be This, That, etc.


I want column P of the *first* row where Smith occurs to say "I found
This!"

In my testing, this happened, as all of mine had THis, That or were empty
which i9s what the Case statement tells it to.

The use of the oCurrent variable was actually a tip I got from you, to
capture the row where I found the first instance of a new name. And of
course it works great.


Now stop that, throwing what I said back at me <VBG. If I recall, you
would use that to save the cell and then use that cell object thereafcter,
but the way you have implemented the code is to save all of the values
separately (i as the row, AgtName as the agent name), so saving the cell
as
well is superfluous, not wrong, just overkill.

(sorry if I broke any rules of netiquette by staring a new
thread...nearly
a
week has passed since I last posted & I thought it unlikely that the
last
would be revisited).


Who cares. I personally think there is a lot of fluff about netiquette.
There are some practices that make life simpler, but they are not rules.

Anyway, I just don't understand the "why" behind it skipping a row on
the
change of name. As long as I'm stuck with this problem, I am trying to
make it a good learning experience...


The why is because you had an If Else Endif. The If evaialuated the
current
row, whereas the Else reset Agtname. But each case looped to the next
item,
so the Else did not also go through the cell evaluation, whereas it does
in
my example because it is an indecent if, before the Case evaluation.

"Patti" wrote in message
...
Bob,

I'm sure this problem is nowhere near as confusing as my explanation my
make
it seem...

For each name in column A, I need to look down a list for various codes in
column L. Once I find a valid code, I want to quit looking, and enter
what
I found (via the case statement) in column P of the first row in which
that
name appeared.

I am confused. It is the Case statement where it is found so the statement
'... Once I find a valid code, I want to quit looking, and enter what I
found (via the case statement) ...' is incompatible as I understand it. And,
the CAse statement sets the Found flag to true whatever the value evaluated!

It has to be in the row with the _first_ instance of the
name because once I get this Sub to work, I am going to delete rows so
that
each name appears only once (keeping the row where the name first
appears).

But are we talking about the firts row with a valid code, or just the first
row for the name?

Oh, I get it now... but how can I reset the Agtname _and_ have it
evaluated
in the first instance?

As I showed you in my follow-up response.

I tried to use "offset" to set it to the name on
the _next_ row, but it still skipped the first instance of a new name.


That's because the If Else makes you skip it as I showed you.

If possible, I'd really like to stick close to the code I've got. I'm
sure
that there is always going to be a technically "better" method, but I
really
need to learn to do this myself, & it's hard to get a handle on when I
keep
"starting over".

But you can't, it doesn't work.