Begin Search at specific cell

  • Thread starter Thread starter John Wilson
  • Start date Start date
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
 
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
 
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...
 
Back
Top