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.
|