Hi all,

I have mixed text which the below function ignores. I know I need t
add Ucase or ProperCase but not sure where to add it.

Function ListSearch(Target As Range, SearchList As Range)
Dim myTarget, mySearchList As Range
Set myTarget = Target
Set mySearchList = SearchList

For Each cell In mySearchList
On Error Resume Next
x = Application.WorksheetFunction.Find(cell.Value, myTarget.Value, 1)
If x 0 Then
If myAnswer < 0 Then myAnswer = myAnswer & ", "
myAnswer = myAnswer & cell.Value
x = 0
End If
Next cell
If myAnswer = 0 Then myAnswer = ""
ListSearch = myAnswer
End Functio

--
VBA Noo
-----------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...fo&userid=3383
View this thread: http://www.excelforum.com/showthread.php?threadid=55454

Nel post
*VBA Noob* ha scritto:

Hi all,

I have mixed text which the below function ignores. I know I need to
add Ucase or ProperCase but not sure where to add it.

Function ListSearch(Target As Range, SearchList As Range)
Dim myTarget, mySearchList As Range
Set myTarget = Target
Set mySearchList = SearchList

For Each cell In mySearchList
On Error Resume Next
x = Application.WorksheetFunction.Find(cell.Value, myTarget.Value, 1)
If x 0 Then
If myAnswer < 0 Then myAnswer = myAnswer & ", "
myAnswer = myAnswer & cell.Value
x = 0
End If
Next cell
If myAnswer = 0 Then myAnswer = ""
ListSearch = myAnswer
End Function

I think it depends on how is your target list, on how is your search list
and also how do you want your results displaied..


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Nel post
*VBA Noob* ha scritto:

Hi all,

I have mixed text which the below function ignores. I know I need to
add Ucase or ProperCase but not sure where to add it.

Function ListSearch(Target As Range, SearchList As Range)
Dim myTarget, mySearchList As Range
Set myTarget = Target
Set mySearchList = SearchList

For Each cell In mySearchList
On Error Resume Next
x = Application.WorksheetFunction.Find(cell.Value, myTarget.Value, 1)
If x 0 Then
If myAnswer < 0 Then myAnswer = myAnswer & ", "
myAnswer = myAnswer & cell.Value
x = 0
End If
Next cell
If myAnswer = 0 Then myAnswer = ""
ListSearch = myAnswer
End Function


Maybe in this way, but I didn't tested it:

Function ListSearch(Target As Range, SearchList As Range)
Dim myTarget, mySearchList As Range
Set myTarget = Target
Set mySearchList = SearchList

For Each cell In mySearchList
On Error Resume Next
x = Application.WorksheetFunction.Find(UCase(cell.Valu e),
UCase(myTarget.Value), 1)
If x 0 Then
If myAnswer < 0 Then myAnswer = myAnswer & ", "
myAnswer = myAnswer & UCase(cell.Value)
x = 0
End If
Next cell
If myAnswer = 0 Then myAnswer = ""
ListSearch = myAnswer
End Function




--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Thanks for your advice but think I solve it myself

Function ListSearch(Target As Range, SearchList As Range)
Dim myTarget, mySearchList As Range
Set myTarget = Target
Set mySearchList = SearchList

For Each cell In mySearchList
On Error Resume Next
x = Application.WorksheetFunction.Find(Application.Pro per(cell.Value),
Application.Proper(myTarget.Value), 1)
If x 0 Then
If myAnswer < 0 Then myAnswer = (myAnswer) & ", "
myAnswer = Application.Proper(myAnswer & cell.Value)
x = 0
End If
Next cell
If myAnswer = 0 Then myAnswer = ""
ListSearch = myAnswer
End Function


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=554546