Begin Search at specific cell

J

John Wilson

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
 
T

Tom Ogilvy

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
 
J

John Wilson

Tom,

Thank you

John


Tom Ogilvy said:
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
Click to expand...
 
Top