Hi All,

Ron recently posted code o Samer on Goolges'
microsoft.public.excel.programming group which Find text in a string
that matches value(s) in a range and if it does find something then it
puts the text string in the cell and if it doesn't then it puts "No
Match". The code was very good.

However, is it possible to modify it to show the position of the text
string in the range of cells. For instance let say I have the following

text starting in range A1:A5

Yellow Orange
Green Grape
Blue Berries
Red Apple
Orange Grapefruit

I want to find the position of the text string "Berries". in the Range
A1:A5. The result I am looking for should produce 3, as it is located
in the3rd row in the range. I do not care about the position of the
actual text "Berries" in the entire string "Blue Berries" just the
position of it in the range A!:A5,

How would I modify the code below to do this?

'----Start of Code-----
Option Explicit

Public Function ShowMatch(CellRef As Range, SrchVals As Range) As
String

Dim iCtr As Integer
Dim cCell As Range
Dim strTestVal As String
Dim lngMatchRef As Long
Dim Bullpen As String
Dim varFindRef As Variant

'Check if target range is only one cell and has contents
If CellRef.Cells.Count 1 Then
ShowMatch = "#InvalidCellRef"
Exit Function
ElseIf Len(CellRef) = 0 Then
'ElseIf Application.WorksheetFunction.IsBlank(CellRef) Then
ShowMatch = "#NoMatch"
Exit Function
End If

'Check if the list to be matched is one dimensional
If SrchVals.Areas.Count 1 Then
ShowMatch = "#TooManyListRngs!"
Exit Function
ElseIf SrchVals.Rows.Count = 1 Or SrchVals.Columns.Count = 1 Then
'Range is one-dimensional
Else
ShowMatch = "#ListRngNot1Dim!"
Exit Function
End If

lngMatchRef = 99999

'Loop through list values
For Each cCell In SrchVals.Cells
strTestVal = CStr(cCell.Value)

If Len(strTestVal) < 0 Then
'List ref cell is not blank, so test the cell
varFindRef = InStr(1, CellRef, strTestVal)
If varFindRef 0 Then
'The list value was found...check it's position in the cell
If varFindRef < lngMatchRef Then
'Use this list value as the first matched value
lngMatchRef = varFindRef
Bullpen = strTestVal
End If
End If
End If
Next cCell
If lngMatchRef = 99999 Then
ShowMatch = "No Match"
Else
ShowMatch = Bullpen
End If
End Function
'----End of Code-----

I would greatly appreciate your help, I am not that good at VBA and
this could save me hundreds of hours of manual data manipulation.

Regards,


Brandon
Email: