Hi,

Im BSLAUTOMATION but @ Home and cant reply to a thread because i forgot
my works password. Anyway


Dave Peterson Wrote:

Can you strip the unnecessary text out of the cell (remove "dinky " in
this
case)?

No because "Dinky" could be anything from a number or random text, see
below for more details

And do you have rules that can be applied in general--always take the
last two
words????

Not to sure at the moment but im trying to find a way and ill explain a
bit better of what im doing!

Eg

Sheet1 name = SORT (Main Page with formula in column B)

Sheet2 name = TOYS
Sheet2 A1 = "Toy Car"
Sheet2 A2 = "Toy Plane"

Sheet3 name = BABY
Sheet3 A1 = "Rattle"
Sheet3 A2 = "Blanket"

*plus 4 other Sheets i wish it to check (6 total)

MainPage with column A as the input
A1 = 1234 Toy Car Dinky B1 = TOYS
A2 = Plastic Rattle WL78 B2 = BABY
**A3 = Blanket with Toy Car B3 = TOYS & BABY


**This could be my only problem because there are 2 keywords on
different sheets

I think its going to need a macro as the Search formula is what im
after but i want to use a reference like (A1) instead of having to use
actual text or "keyword"

Can i change the reference or value of A1 and name it AA and then use
that in the formula or im i back to the macro??

Anyhelp please ?

Cheers

Craig


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

I don't know if it's possible using worksheet formulas--well, I know it's not
possible for me!

You could post your question in .worksheet.functions to see if anyone can help.
(Those people live for this kind of stuff.) And after seeing that "B3 = TOYS &
BABY", I'm not sure if it could be done in a formula.

Me on the other hand, I'd cheat and use a UserDefined function.

Do you want to try that?

If yes, paste this code into a general module of your workbook (more about that
later).

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


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.

Then use a formula like:

=mysearch(A1,Toys!A:A,baby!A:A,'Other Sheet!A:A)




atxcomputers wrote:

Hi,

Im BSLAUTOMATION but @ Home and cant reply to a thread because i forgot
my works password. Anyway

Dave Peterson Wrote:

Can you strip the unnecessary text out of the cell (remove "dinky " in
this
case)?

No because "Dinky" could be anything from a number or random text, see
below for more details

And do you have rules that can be applied in general--always take the
last two
words????

Not to sure at the moment but im trying to find a way and ill explain a
bit better of what im doing!

Eg

Sheet1 name = SORT (Main Page with formula in column B)

Sheet2 name = TOYS
Sheet2 A1 = "Toy Car"
Sheet2 A2 = "Toy Plane"

Sheet3 name = BABY
Sheet3 A1 = "Rattle"
Sheet3 A2 = "Blanket"

*plus 4 other Sheets i wish it to check (6 total)

MainPage with column A as the input
A1 = 1234 Toy Car Dinky B1 = TOYS
A2 = Plastic Rattle WL78 B2 = BABY
**A3 = Blanket with Toy Car B3 = TOYS & BABY


**This could be my only problem because there are 2 keywords on
different sheets

I think its going to need a macro as the Search formula is what im
after but i want to use a reference like (A1) instead of having to use
actual text or "keyword"

Can i change the reference or value of A1 and name it AA and then use
that in the formula or im i back to the macro??

Anyhelp please ?

Cheers

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

Dave,

Absolutly fantastic
you are amazing
This will save me hours of sorting these lists
Thanks everso much
I thought it couldnt be done using a formula - well i cant do anyway -
theres probably some guru out there with that knowledge.

Thanks once again

Craig


Dave Peterson Wrote:
I don't know if it's possible using worksheet formulas--well, I know
it's not
possible for me!

You could post your question in .worksheet.functions to see if anyone
can help.
(Those people live for this kind of stuff.) And after seeing that "B3
= TOYS &
BABY", I'm not sure if it could be done in a formula.

Me on the other hand, I'd cheat and use a UserDefined function.

Do you want to try that?

If yes, paste this code into a general module of your workbook (more
about that
later).

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


If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.

Then use a formula like:

=mysearch(A1,Toys!A:A,baby!A:A,'Other Sheet!A:A)




atxcomputers wrote:

Hi,

Im BSLAUTOMATION but @ Home and cant reply to a thread because i
forgot
my works password. Anyway

Dave Peterson Wrote:

Can you strip the unnecessary text out of the cell (remove "dinky "
in
this
case)?

No because "Dinky" could be anything from a number or random text,
see
below for more details

And do you have rules that can be applied in general--always take
the
last two
words????

Not to sure at the moment but im trying to find a way and ill
explain a
bit better of what im doing!

Eg

Sheet1 name = SORT (Main Page with formula in column B)

Sheet2 name = TOYS
Sheet2 A1 = "Toy Car"
Sheet2 A2 = "Toy Plane"

Sheet3 name = BABY
Sheet3 A1 = "Rattle"
Sheet3 A2 = "Blanket"

*plus 4 other Sheets i wish it to check (6 total)

MainPage with column A as the input
A1 = 1234 Toy Car Dinky B1 = TOYS
A2 = Plastic Rattle WL78 B2 = BABY
**A3 = Blanket with Toy Car B3 = TOYS & BABY


**This could be my only problem because there are 2 keywords on
different sheets

I think its going to need a macro as the Search formula is what im
after but i want to use a reference like (A1) instead of having to
use
actual text or "keyword"

Can i change the reference or value of A1 and name it AA and then
use
that in the formula or im i back to the macro??

Anyhelp please ?

Cheers

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


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

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

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

Dave Peterson Wrote:
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

Hi Dave,

Cheers for the reply but id already sorted it.

Great code....Cheers again

Craig


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

Hi Dave,

Ive been playing with this for a few days now and im really happy with
it, except for 1 small thing thats bothering me.

Some of the searches dont give me the correct answer because of the
below:

Sheet1
A1 = 789dinky1977
B2 answer = Toys & 789dinky

A2 = 789dinky1966
B2 answer = Toys & 789dinky

Sheet2 A:A

789dinky
789dinky1977
789dinky1966

It finds the first match and then displays that answer regardless of
whats after

Is there anyway to resolve this ??

Craig

atxcomputers Wrote:
Dave Peterson Wrote:
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


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

Does resolve mean you want to list all the found cells on each sheet?

If it does, then take a look at VBA's help for Find. It shows how to find the
first, keep track of that location, and keep looking.

If that's not what you mean, post back.

(I've kind of lost track of what you're really after.)

BSLAUTOMATION wrote:

Hi Dave,

Ive been playing with this for a few days now and im really happy with
it, except for 1 small thing thats bothering me.

Some of the searches dont give me the correct answer because of the
below:

Sheet1
A1 = 789dinky1977
B2 answer = Toys & 789dinky

A2 = 789dinky1966
B2 answer = Toys & 789dinky

Sheet2 A:A

789dinky
789dinky1977
789dinky1966

It finds the first match and then displays that answer regardless of
whats after

Is there anyway to resolve this ??

Craig

atxcomputers Wrote:
Dave Peterson Wrote:
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

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

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

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

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

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

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