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

"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 are:
' * 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