Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Function to search for text (2003)

    I need to test for the presence of a single word ("tank" in the following example) in each cell of a range making up a long column. If the word is found, I want the formula to return "Match." If not, I want a blank. I can write a formula easily enough to do this as

    =IF(ISERROR(SEARCH("tank",F27)),"","Match")

    using the ISERROR function to test for whether the match is found.

    But I want other users to be able to do this and this is a bit complicated, so I would like to write a custom formula to accomplish the same thing. I have written

    Public Function IsIn(SearchText As String, cell) As String
    If IsError(Application.WorksheetFunction.Search(Searc hText, cell)) Then
    IsIn = ""
    Else
    IsIn = "Match"
    End If
    End Function

    But when I apply this to the same data as the formula above, cells which do not contain the word I am looking for return a #VALUE! error instead of the blank. What is wrong with my VBA error checking?

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Custom Function to search for text (2003)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> DickGorski

    OK what is wrong with the Find tool built in MS-Excel?

    You can use this to find all the occurrences of the text you are looking for, and also make changes such as replace the text found with a desired text, or nothing at all.

    I am envisioning code that would use something like this: <font color=red>(FYI: This was recorded in a hurry, so you have an example. This code will need to be <font color=blue>cleaned up</font color=blue>)</font color=red>

    Selection.Find(What:="tank", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate

    See how many "variables" you can control. You really can use this one.

    Cordially

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Function to search for text (2003)

    I think that doing it this way would be much more efficient:

    <code>
    Public Function IsIn(SearchText As String, oCell As Range) As String
    If oCell.Cells.Count > 1 Then
    IsIn = "Error"
    Exit Function
    End If
    If InStr(oCell, SearchText) > 0 Then
    IsIn = "Match"
    Else
    IsIn = ""
    End If
    End Function
    </code>
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Aug 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Function to search for text (2003)

    Thanks for 2 great answers. Legare's is very neat and efficient. Wassim's solution is very flexible. I will put both to good use.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •