Function

[nc]

I have this function below, it's purpose is to search text from a list on
Sheets("Input_Data") and return the text. Can you please modify the code to
select the range of the of the list when using the function.


Dim excelapp As Excel.Application
Dim excelWb As Excel.Workbook
Dim excelSheet As Excel.Worksheet

Function SearchTxt(MyText As String, Text_Length As Integer, Search_Guess As
Integer) As String

'MyText is the cell you want to search.
'Text_Length allows you to vary the length of the text returned
'Search_Guess can be used if you want to start the search at a particular
point in the cell

Dim intI As Integer
Dim SearchItem(100) As String
Dim LenText As Integer
Dim n2 As Integer
Dim n As Integer
Dim Mk1 As Integer

' Initialise array with values from column A of Input_Data work sheet -
allows 100 different codes
For intI = 1 To 100
SearchItem(intI) = Sheets("Input_Data").Cells([intI], [1])
Next

LenText = Len(MyText)

For n2 = 1 To 100 Step 1


For n = Search_Guess To LenText Step 1
If Mid(MyText, n, Text_Length) = SearchItem(n2) Then
Mk1 = n
End If
Next n

Next

SearchRL = Mid(MyText, Mk1, Text_Length)
MyEndBit:
End Function