Begin Search at specific cell
 

Could use a little help with this.

Here's the code that I'm using:

Range("Division").Select
On Error Resume Next
Selection.Find(What:=FindTeamName, After:=ActiveCell, _
MatchCase:=False).Activate
DivNo1 = ActiveCell.Offset(0, -1).Column
Selection.Find(What:=FindTeamName, After:=ActiveCell, _
MatchCase:=False).Activate
DivNo2 = ActiveCell.Offset(0, -1).Column

What I'm trying to accomplish..........
"FindTeamName" may appear once or more in my range
"Divisions" (8 rows by 9 columns).
With my first "Find" above, I want to get the Column -1 of
where it's found. Problem is that I want the second "Find"
to search from where the first is found only to the end of that range.
As of now, if there isn't a second occurance of "FindTeamName"
the second "Find" returns the Column of the first "Find".

Not sure if I explained that well enough?
Any help would be appreciated.

Thanks,
John

Begin Search at specific cell
 

Sub AABBCC()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Dim DivNo1, DivNo2
Dim FindTeamName As String
FindTeamName = "A"
Set rng = Range("Division")
Set rng1 = rng.Find(What:=FindTeamName, After:=rng(1), _
MatchCase:=False)
If Not rng1 Is Nothing Then
DivNo1 = rng1.Offset(0, -1).Column
Set rng2 = Range(rng1.Offset(1, 0), rng(rng.Count))
Set rng3 = rng2.Find(What:=FindTeamName, After:=rng2(1), _
MatchCase:=False)
If Not rng3 Is Nothing Then
DivNo2 = rng3.Offset(0, -1).Column
End If
End If
Debug.Print rng1.Address, rng3.Address
Debug.Print DivNo1, DivNo2

End Sub


--
Regards,
Tom Ogilvy



John Wilson wrote in message
...
Could use a little help with this.

Here's the code that I'm using:

Range("Division").Select
On Error Resume Next
Selection.Find(What:=FindTeamName, After:=ActiveCell, _
MatchCase:=False).Activate
DivNo1 = ActiveCell.Offset(0, -1).Column
Selection.Find(What:=FindTeamName, After:=ActiveCell, _
MatchCase:=False).Activate
DivNo2 = ActiveCell.Offset(0, -1).Column

What I'm trying to accomplish..........
"FindTeamName" may appear once or more in my range
"Divisions" (8 rows by 9 columns).
With my first "Find" above, I want to get the Column -1 of
where it's found. Problem is that I want the second "Find"
to search from where the first is found only to the end of that range.
As of now, if there isn't a second occurance of "FindTeamName"
the second "Find" returns the Column of the first "Find".

Not sure if I explained that well enough?
Any help would be appreciated.

Thanks,
John

Begin Search at specific cell
 

Tom,

Thank you

John


"Tom Ogilvy" wrote in message
...
Sub AABBCC()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Dim DivNo1, DivNo2
Dim FindTeamName As String
FindTeamName = "A"
Set rng = Range("Division")
Set rng1 = rng.Find(What:=FindTeamName, After:=rng(1), _
MatchCase:=False)
If Not rng1 Is Nothing Then
DivNo1 = rng1.Offset(0, -1).Column
Set rng2 = Range(rng1.Offset(1, 0), rng(rng.Count))
Set rng3 = rng2.Find(What:=FindTeamName, After:=rng2(1), _
MatchCase:=False)
If Not rng3 Is Nothing Then
DivNo2 = rng3.Offset(0, -1).Column
End If
End If
Debug.Print rng1.Address, rng3.Address
Debug.Print DivNo1, DivNo2

End Sub


--
Regards,
Tom Ogilvy



John Wilson wrote in message
...
Could use a little help with this.

Here's the code that I'm using:

Range("Division").Select
On Error Resume Next
Selection.Find(What:=FindTeamName, After:=ActiveCell, _
MatchCase:=False).Activate
DivNo1 = ActiveCell.Offset(0, -1).Column
Selection.Find(What:=FindTeamName, After:=ActiveCell, _
MatchCase:=False).Activate
DivNo2 = ActiveCell.Offset(0, -1).Column

What I'm trying to accomplish..........
"FindTeamName" may appear once or more in my range
"Divisions" (8 rows by 9 columns).
With my first "Find" above, I want to get the Column -1 of
where it's found. Problem is that I want the second "Find"
to search from where the first is found only to the end of that range.
As of now, if there isn't a second occurance of "FindTeamName"
the second "Find" returns the Column of the first "Find".

Not sure if I explained that well enough?
Any help would be appreciated.

Thanks,
John