VBA - Excel：比较单词时摆脱区分大小写？ [英] VBA - Excel : get rid of the case sensitivity when comparing words?

问题描述

我正在处理一个VBA程序，它将允许用户键入一个地址，并通过匹配地址元素与数据库来查找位置。

不幸的是，我遇到了大小写敏感的复发问题。

例如，当我使用这段代码时：

  i = 11 To lRowB 
 Range（B& i）.Activate 
 myResult = IsNumeric（Application.Match（ActiveCell.Value，manilaListRange，0））
  

将活动单元格的值与我的数据库中的单词列表进行比较。问题是，如果在我的活动单元格中，这个词是迈阿密或迈阿密，而只有迈阿密在数据库中，它将不起作用。

其他示例：

  If Range（J6）。Value =tawiThen 
 Range（J6 ）.Value =Tawi-Tawi
如果
  

同样的问题，只有用相同的案例写的字将会工作。

我如何摆脱这个？这是特别烦人的，我不能重写我的数据库在每种情况下组合可能！

提前感谢

解决方案

问题在模块级别：

 选项比较文本
  

这使得所有文本比较不区分大小写。这意味着以下代码将显示消息这是真的：

 选项对比文本
 
 Sub testCase（）
如果UPPERcase=upperCASE然后
 MsgBox这是真的：选项比较文本已经设置！ 
 End If 
 End Sub 
  

参见例如 http://www.ozgrid.com/VBA/vba-case-sensitive.htm 。我不知道它会完全解决所有实例的问题（例如 Application.Match 函数），但它会照顾所有的如果a = b 语句。对于 Application.Match - 您可能需要使用 LCase 函数将参数转换为大写或小写。

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 !

解决方案

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.

这篇关于VBA - Excel：比较单词时摆脱区分大小写？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！