This line builds the string to return:

myStr = myStr & " & " & myRealRng.Parent.Name

Maybe you could just change it to:

myStr = myStr & " & " & myRealRng.Parent.Name & "--" & mycell.value


(I didn't test it.)

atxcomputers wrote:

Hi

The below U/D function matches a string of characters from a given cell
to text on other sheets and then tells me which sheets it is on.

I would like to find out what it has found but cant seem find how to
get that result.

I have highlighted the bit that does the compare and feel it has
something to do with this

Dave Peterson Wrote:


Option Explicit
Function mySearch(mySearchCell As Range, ParamArray myRng()) As String

Dim myCell As Range
Dim myRealRng As Range
Dim myElement As Variant
Dim myStr As String

myStr = ""

For Each myElement In myRng
If TypeOf myElement Is Range Then
'do the work
Set myRealRng = Nothing
On Error Resume Next
Set myRealRng = Intersect(myElement, myElement.Parent.UsedRange)
On Error GoTo 0

If myRealRng Is Nothing Then
'do nothing
Else
For Each myCell In myRealRng.Cells
If IsEmpty(myCell) Then
'do nothing
Else
If InStr(1, mySearchCell, myCell.Value, _
vbTextCompare) 0 Then
myStr = myStr & " & " & myRealRng.Parent.Name
Exit For
End If
End If
Next myCell
End If
End If
Next myElement

If myStr = "" Then
myStr = "Not Found!"
Else
myStr = Mid(myStr, 4)
End If

mySearch = myStr

End Function

I tried changing myRealRng.Parent.Name but just came back with VALUE#

Hopefully Dave Peterson will help me as he did the above function but
if anyone else can help then please do so


Thanks in advance

Regards

Craig

--
atxcomputers
------------------------------------------------------------------------
atxcomputers's Profile: http://www.excelforum.com/member.php...o&userid=26852
View this thread: http://www.excelforum.com/showthread...hreadid=400972

--

Dave Peterson