VBA – Excel : get rid of the case sensitivity when comparing words?

calendar_today Asked Jun 11, 2013
thumb_up 30 upvotes
history Updated April 16, 2026

Question posted 2013 · +15 upvotes

I am working on an VBA program which would allow the user to type an address and find the location by matching elements of the address with a database.

Unfortunately, I am having a recurrent problem with the case sensitivity.

For example, when I am using this code : 

For i = 11 To lRowB
Range("B" & i).Activate
myResult = IsNumeric(Application.Match(ActiveCell.Value, manilaListRange, 0))

It is gonna compare the value of the active cell to a list of words from my database. Problem is, if in my active cell the word is “miami” or “MIAMI” and only “Miami” is in the database, it won’t work…

Other example: 

If Range("J6").Value = "tawi" Then
Range("J6").Value = "Tawi-Tawi"
End If

Same problem, only the word written with the same case is gonna work.

How can I get rid of this? It’s particularly annoying and I can’t rewrite my database in every case combination possible!

Thanks in advance !

Accepted answer +30 upvotes

There is a statement you can issue at the module level:

Option Compare Text

This makes all “text comparisons” case insensitive. This means the following code will show the message “this is true”:

Option Compare Text

Sub testCase()
  If "UPPERcase" = "upperCASE" Then
    MsgBox "this is true: option Compare Text has been set!"
  End If
End Sub

See for example http://www.ozgrid.com/VBA/vba-case-sensitive.htm . I’m not sure it will completely solve the problem for all instances (such as the Application.Match function) but it will take care of all the if a=b statements. As for Application.Match – you may want to convert the arguments to either upper case or lower case using the LCase function.

2 code variants in this answer

  • Variant 1 — 1 lines, starts with Option Compare Text
  • Variant 2 — 7 lines, starts with Option Compare Text

VBA Core objects referenced (5)

Top vba Q&A (6)

+30 upvotes ranks this answer #11 out of 81 vba solutions on this site — top 14%.
vba