another loop question

[Patti]

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

 

[cucchiaino]

"Patti" <MrsT@SPAM_NOT.com> ha scritto nel messaggio

Try this.

---------------------------
Option Explicit
Private Sub testloop()

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

lstrow = Range("a" & Rows.Count).End(xlUp).Row
papi = Range("A2").Text
For i = 2 To lstrow
agtname = Range("a" & i).Text
If agtname = papi 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
papi = agtname
MsgBox "moved on!"
i = i - 1
End If
Next i
End Sub
------------------

 

[Bob Phillips]

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]

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]

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]

Excellent Bob. Thanks a million!

Patti

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)