Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search a cell for a specific number | Excel Worksheet Functions | |||
count cells that begin with specific text | Excel Worksheet Functions | |||
Search for record with specific name in a cell | Excel Worksheet Functions | |||
How do I search for specific text and sum the cell to the right? | Excel Worksheet Functions | |||
Search for a specific character in a cell in an if statement | Excel Worksheet Functions |