InStr Function to search not case sensitive

[sermest]

Hello,

I am using InStr function, but it is searching case sensititive. I want to search not case sensitive. My function is like below. Can you help?

Function ContainsText(Rng As Range, Text As String) As String
Dim T As String
Dim myCell As Range
For Each myCell In Rng
If InStr(myCell.Text, Text) > 0 Then
If Len(T) = 0 Then
T = myCell.Address(False, False)
Else
T = T & "," & myCell.Address(False, False)
End If
End If
Next myCell
ContainsText = T
End Function

Thanks in advance.
sermest

 

[Norie]

Why are you using code to do this?

You could use worksheet functions.

 

[Joe4]

Whether you do this in VBA, or in Excel functions (as Norie suggested), if you do not care about case, you can get around this by converting both pieces to Upper case before comparing.

In Excel, that would be the UPPER function.
In VBA, it is UCASE.

To use it in VBA int he context of your function would look something like:
If InStr(Ucase(myCell.Text), Ucase(Text)) > 0 Then

 

[sermest]

Thanks your code helped better.

Best,
sermest

 

[JoeCross]

This is a very old post, but if others read it, you can also do this with the built in functionality of Instr().
The Instr function defaults the compare parameter to "vbbinarycompare" which is case sensitive, but you can change it to "vbtextcompare", which is not case sensative.

It would look like
If InStr(1,myCell.Text, Text,vbtextcompare) > 0 Then

and this would return true if "Text" is contained within myCell.text.
-Joe

 

[ChrisRaisin]

Just further to JoeCross's comment above (and to set the record straight) the Instr function defaults to the system-wide default setting (which may be vbTextCompare or may be vbBinaryCompare). (refer to the old "set compare to" command). It is best to either change the default to vbTextCompare or to implicitly include that parameter in your InStr() call (ie, if you are not concerned with the text foiund matches exactly in it "case" - Ucase, or lcase)

 

[JoeCross]

Thanks for the clarification Chris.
I did not know you can change your global default. That's handy!