Excellent Bob. Thanks a million!

Patti




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

When the cell moves on, set a range object to that cell, and then you have
everything about the cell, value, row, column, what it had for breakfast.

The address can then be had with
oCurrent.Address(False,False)

Private Sub testloop()

Dim lstrow As Long
Dim i As Long
Dim j As Long
Dim agtname As String
' next row is new
Dim oCurrent As Range

lstrow = Range("a" & Rows.Count).End(xlUp).Row
agtname = Range("A1").Value
' next row is new
Set oCurrent = Range("A1")
For i = 2 To lstrow
If Cells(i, "A") = agtname Then
If Cells(i, 2).Value < "" Then
Select Case Cells(i, 2)
Case "This"
MsgBox "Current Type for " & agtname & " is blah
blah blah " & i
Case "That"
MsgBox "Current Type for " & agtname & " is yada
yada yada " & i
Case Else
MsgBox "Current Type for " & agtname & " is " &
Cells(i, 2) & " " & i
End Select
End If
Else
MsgBox "moved on!"
agtname = Cells(i, "A").Value
' next row is new
Set oCurrent = Cells(i, "A")
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" wrote in message
...
Yep, that fixed it. Thanks Bob and cucchiaino!

Anyone know how to grab the cell address of the *first* instance for
each
name where agtname = Cells(i, "A").Value? From original question
below:

"For example, say agtname becomes Patti on row 5, and stays Patti until
in
turns to Bob on row 7. I want to hold a variable for Patti called
something like "firstfound" with an address of A5. When I get to Bob,
"firstfound" will become A7."

In the meantime, I'll keep tinkering!

Patti


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

Have a shot at this

Private Sub testloop()

Dim lstrow As Long
Dim i As Long
Dim j As Long
Dim agtname As String

lstrow = Range("a" & Rows.Count).End(xlUp).Row
agtname = Range("A1").Value
For i = 2 To lstrow
If Cells(i, "A") = agtname Then
If Cells(i, 2).Value < "" Then
Select Case Cells(i, 2)
Case "This"
MsgBox "Current Type for " & agtname & " is
blah
blah blah " & i
Case "That"
MsgBox "Current Type for " & agtname & " is
yada
yada yada " & i
Case Else
MsgBox "Current Type for " & agtname & " is "
&
Cells(i, 2) & " " & i
End Select
End If
Else
MsgBox "moved on!"
agtname = Cells(i, "A").Value
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" wrote in message
...
Sorry to be a pain...still working on building my loop. Column A
contains
names, including the some of the same names consecutively. I would
expect
that if name in A is the same for the first three rows, the Case
statements
would execute, provided that the cells in column B are not null, but
when
I
get to a new name in the 4th row, I should get the message box
saying
"moved
on!"

As it is, it just keeps going though the entire list even though the
name
(agtname) changes.

I also tried to wrap the "If Cells(i, 2).Value < "" Then" and Case
statement in something like "Do while agtname = agtname" but it
hangs
up.

In a nutshell, I need to know how to set my agtname variable as the
value/text in "i" of column A, and keep it until I run across a new
name,
which then becomes agtname. Also, how do i capture the row address
I
was
on
when I agtname changed so that I can use it later? For example, say
agtname
becomes Patti on row 5, and stays Patti until in turns to Bob on row
7.
I
want to hold a variable for Patti called something like "firstfound"
with
an
address of A5. When I get to Bob, "firstfound" will become A7.

I hope that's clear!

TIA


Option Explicit
Private Sub testloop()

Dim lstrow As Long
Dim i As Long
Dim j As Long
Dim agtname As String

lstrow = Range("a" & Rows.Count).End(xlUp).Row
For i = 2 To lstrow
agtname = Range("a" & i).Text
If agtname = agtname Then
If Cells(i, 2).Value < "" Then
Select Case Cells(i, 2)
Case "This"
MsgBox "Current Type for " & agtname & " is blah
blah
blah
" & i
Case "That"
MsgBox "Current Type for " & agtname & " is yada
yada
yada
" & i
Case Else
MsgBox "Current Type for " & agtname & " is " &
Cells(i,
2)
& " " & i
End Select
End If
Else
MsgBox "moved on!"
End If
Next i
End Sub