A function to find a phrase within a range of cells and return the address of the first instance.
 

I have been trying to write a UDF that takes a cell range and a string
as inputs. It then checks each cell in the supplied range for the
string returning the address (i.e. $A$6) of the first instance.

I won't embarass myself by putting any of my attempts here. Finding
and selecting the cell seems easy to do as a sub-routine but I've
struggled mightily to do this as a function. I'd really appreciate any
help.

A function to find a phrase within a range of cells and return the
 

Thanks for the quick reply. I managed to hash the following together
which works admirably:

Function FindInRange(Range As Range, What2Find As String) As String
' Finds What2Find in Range and returns address of first instance

FindInRange = Range.Find(What:=What2Find,
after:=Range(Range.Count)).Address

End Function

I can't believe it took me so long!

A function to find a phrase within a range of cells and return the
 

Thanks Tom - that works perfectly too.

I extended this so that it will also return the name of the worksheet
that the data was found in. There is probably a neater way of doing
this.

Public Function FindText(rng As Range, s As String)
Dim cell As Range
Dim sht As Variant
Dim rng2 As Variant

FindText = "Not Found"

For Each cell In rng
If InStr(1, cell, s, xlTextcompare) Then
sht = cell.Worksheet.Name
rng2 = cell.Address
FindText = "'" & sht & "'!" & rng2
Exit For
End If
Next

End Function

Tom Ogilvy wrote:
public Function FindText(rng as Range, s as String)
Dim cell as Range,
FindText = "Not Found"
for each cell in rng
if instr(1,cell,s,xlTextcompare) then
FindText = cell.Address
exit for
end if
Next
End Function

A UDF used in a worksheet can not select a cell. It can only return a value
to be displayed in the cell in which it is located - just like any built in
function.

--
Regards,
Tom Ogilvy


" wrote:

I have been trying to write a UDF that takes a cell range and a string
as inputs. It then checks each cell in the supplied range for the
string returning the address (i.e. $A$6) of the first instance.

I won't embarass myself by putting any of my attempts here. Finding
and selecting the cell seems easy to do as a sub-routine but I've
struggled mightily to do this as a function. I'd really appreciate any
help.