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 !
This question is related to
vba
excel
case-sensitive
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.
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
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
Source: Stackoverflow.com