In VBA get rid of the case sensitivity when comparing words

Question

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   amp  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

User · Accepted Answer

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.

User · Answer

If the list to compare against is large   ie the manilaListRange range in the example above   it is a smart move to use the match function  It avoids the use of a loop which could slow down the procedure  If you can ensure that the manilaListRange is all upper or lower case then this seems to be the best option to me  It is quick to apply  UCase  or  LCase  as you do your match    If you did not have control over the ManilaListRange then you might have to resort to looping through this range in which case there are many ways to compare  search    Instr    replace  etc

User · Answer

It is a bit of hack but will do the task   Function equalsIgnoreCase str1 As String  str2 As String  As Boolean     equalsIgnoreCase   LCase str1    LCase str2  End Function

User · Answer

You can convert both the values to lower case and compare   Here is an example   If LCase Range  J6   Value    LCase  Tawi   Then    Range  J6   Value    Tawi-Tawi  End If

[vba] In VBA get rid of the case sensitivity when comparing words?

Examples related to vba

Examples related to excel

Examples related to case-sensitive