Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search column - return row found in long text string
All -
Looking for a way to search column C for a 9 digit number that resides in cell A1. I would like to return the row number of any matches to cell B1. I use the =MATCH function which works except some of the cells in column C contain text greater than 255 characters (which appears to be the functions limit) and the function errors out. Any ideas? Any help is appreciated. -Jay |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search column - return row found in long text string
"JayL" wrote:
Looking for a way to search column C for a 9 digit number that resides in cell A1. I would like to return the row number of any matches to cell B1. You can use Find and FindNext and some supporting logic. The hard part is dealing with the lack of a no-wrap Find option like there is in Word. You have to manually keep track of what you've already found, then stop searching when you make it back to the first occurrence. Something along the lines of this will help. You may want to figure out something better to do with the results than dump them in a cell. Sub FindInColumnC() Dim rng As Range, found As Range, firstFound As Range Dim Looped As Boolean Dim results As String Set rng = Range("C:C") Looped = False Set found = Range("C1") ' now look for first instance of what's in A1 in column C ' The arguments to Find a ' * what to find, ' * where to start looking, ' * looking at what? formulas or values... ' * match in part of cell, or match the whole cell ' * searching by column or row ' there are others if they strike your fancy. ' ' Find returns the range where it finds what it's looking for ' or Nothing if it finds... nothing. Set found = rng.Find(Range("A1").Value, found, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns) If (Not (found Is Nothing)) Then Set firstFound = found ' remember where first instance is results = found.Row Else Exit Sub ' there are no instances End If ' now look for other instances Do Until (found Is Nothing) Or (Looped) Set found = rng.FindNext(found) If found.Address = firstFound.Address Then Looped = True ' back where we started, so quit Else If (Not (found Is Nothing)) Then Debug.Print found.Address results = results & " " & found.Row End If End If Loop Range("B2").Value = results End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested formula to search a text string and return specific text | Excel Worksheet Functions | |||
return text value found most frequently in a column | Excel Worksheet Functions | |||
Search text string for ssn and copy to new column | Excel Discussion (Misc queries) | |||
search for date in long string of text | Excel Discussion (Misc queries) | |||
Return text found in a search | Excel Worksheet Functions |