Creating searchtxt-function

[Temporary-Failure]

I want to make own function which is like =SEARCHTXT("BLAAH";A1)

If A1 includes somewhere text BLAAH, function gives TRUE otherwise FALSE.

Help me please.

 

[VoG]

Try

Function searchtxt(txt As String, r As Range) As Boolean
searchtxt = InStr(r.Value, txt) > 0
End Function

 

[Temporary-Failure]

Perfect, thanks.

 

[Temporary-Failure]

Try

Function searchtxt(txt As String, r As Range) As Boolean
searchtxt = InStr(r.Value, txt) > 0
End Function

How to change code to search text from an area? For example =searchtxt("BLAAH";A1:B100)?

 

[VoG]

Try

Function searchtxt(txt As String, r As Range) As Boolean
Dim c As Range
For Each c In r
    searchtxt = InStr(c.Value, txt) > 0
    If searchtxt Then Exit Function
Next c
End Function

 

[Richard Schollar]

How to change code to search text from an area? For example =searchtxt("BLAAH";A1:B100)?

There isn't any need to do this in VBA though as:

=COUNTIF(Range,"*"&"BLAAH"&"*")>0

will achieve the same thing (it isn't case-sensitive though).

 

[Temporary-Failure]

VBA function is shorter and faster to use.

 

[VoG]

VBA function is shorter and faster to use.

Have you actually timed it on a large range?

 

[Richard Schollar]

VBA function is shorter and faster to use.

I'd be greatly surprised if that was the case

 

[Temporary-Failure]

=COUNTIF(Range,"*"&"BLAAH"&"*")>0
vs.
=TXT(BLAAH;range)

quite obvious.