typo alert:

If foundctrl = MaxFound Then
should be
If foundctr = MaxFound Then

sorry.

Dave Peterson wrote:

I don't see how it could keep looping--I do see how it could take a lot longer,
though.

And the other bad thing is I'm not sure what the current code looks like.

I went back to one of the first posts and changed it to look like:

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
Dim FoundCtr As Long
Dim MaxFound As Long

MaxFound = 10

myStr = ""

For Each myElement In myRng
If TypeOf myElement Is Range Then
FoundCtr = 0

'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
FoundCtr = FoundCtr + 1
If foundctrl = MaxFound Then
Exit For
End If
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

=========
But if I recall correctly, you wanted the string changed.

If this doesn't help, post the current version of your code.

BSLAUTOMATION wrote:

Dave,

Tried this and it works but it doesnt stop the loop and keeps
calculating, is there anywhere else i can put the "Exit For" to stop
it looping.

Or could i tell it to loop 10 times and then exit??

Sorry to be a pain but i feel this is close to completion

Regards

Craig

Dave Peterson Wrote:
I mis-remembered what the code did. I thought it use .find, but going
back
through the thread shows that it's just looping through the cells.


The "exit For" statement means that it stops looking.

So try commenting that line out and recalculating.





BSLAUTOMATION wrote:

Hi Dave,

Sorry to bother you all the time

Basically, the U/D formula searches the 7 sheets and tells me what
sheet it was on and also what it found (as you know)

Quote

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

This does this fine only if the "mycell.value" is unique otherwise
it
displays the shortest match.

Example

Sheet1

A1 = 789dinky1966
A2 = 789dinky1977
A3 = 789dinky

U/D function Answers

B1 = TOYS & 789dinky
B2 = TOYS & 789dinky
B3 = TOYS & 789dinky

Sheet 2 (TOYS)

A1 = 789dinky <-------------- Problem! Matches and
displays
this for all
A2 = 789dinky1966 <-------------- Wont pickup the date because of
the above
A3 = 789dinky1977 <-------------- As above

As you can see it displays the first match (highlighted in blue), and
i
have to manually go through them to sort them out

As a short fix i have changed "789dinky" to "789dinky-" and it then
works but it then doesnt find 789dinky on its own

I have quite a few like this so the above short term fix isnt
anygood

Any help Dave?

Kind Regards

Craig

--
BSLAUTOMATION

------------------------------------------------------------------------
BSLAUTOMATION's Profile:
http://www.excelforum.com/member.php...fo&userid=7611
View this thread:
http://www.excelforum.com/showthread...hreadid=400972

--

Dave Peterson

--
BSLAUTOMATION
------------------------------------------------------------------------
BSLAUTOMATION's Profile: http://www.excelforum.com/member.php...fo&userid=7611
View this thread: http://www.excelforum.com/showthread...hreadid=400972

--

Dave Peterson

--

Dave Peterson