Proper Case in Function

[VBA Noob]

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

 

[Franz Verga]

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

 

[Franz Verga]

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.Value),
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

 

[VBA Noob]

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