vba 使用多个字符串条件搜索 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6324737/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Search Excel with multiple string criteria
提问by Ankit
I'd like to search a description cell for ANY words (criteria) from a predefined list, and pull the relevant dollar amount for the item from the adjacent cell.
我想从预定义列表中搜索任何单词（标准）的描述单元格，并从相邻单元格中提取该项目的相关美元金额。
To illustrate, here is my setup:
为了说明，这是我的设置：
A | B | C | D | E | F |
Date | Descr.| Amount| Cat1 | Cat2 | Cat3 |
D,E,F: Each one of these are item categories. If the item in that row belongs to a category, I would like its amount to populate that category cell in that same row. Each category cell has a formula to test for this and perform the operation.
D、E、F：每一项都是项目类别。如果该行中的项目属于一个类别，我希望它的数量填充同一行中的该类别单元格。每个类别单元格都有一个公式来测试并执行操作。
My first thought was to use VLookup, but I have a list of lookup values. I haven't been able to successfully do that. Each of the category cells has a formula like this -->
我的第一个想法是使用 VLookup，但我有一个查找值列表。我没能成功做到这一点。每个类别单元格都有一个这样的公式 -->
=IF(SUM(COUNTIF($B10,{"*costco*","*saputo*","*t & t*"}))>0,$C10," ")
=IF(SUM(COUNTIF($B10,{"*costco*","*saputo*","*t & t*"}))>0,$C10," ")
Column B refers to the description, which is searched for any of the criteria. If any of the words are contained in the description, the cell is filled with the amount from column C, otherwise its left with a space.
B 列指的是描述，它是针对任何条件进行搜索的。如果描述中包含任何单词，则单元格填充 C 列中的金额，否则其左侧有一个空格。
This works perfectly as is, however I need the criteria (costco, saputo, t&t in this case) to be flexible (add or remove strings to the list) rather than hard-coded in the formula. This is my issue: the formula stops working if I replace the criteria with a reference to another cell. I'm relatively new to excel and not familiar with restrictions/constraints of its functions, which I suspect is the issue.
这按原样完美运行，但是我需要标准（在这种情况下为 costco、saputo、t&t）灵活（在列表中添加或删除字符串），而不是在公式中进行硬编码。这是我的问题：如果我用对另一个单元格的引用替换条件，则公式将停止工作。我对 excel 比较陌生，不熟悉其功能的限制/约束，我怀疑这是问题所在。
Any help is much appreciated, let me know if I need to provide more info.
非常感谢任何帮助，如果我需要提供更多信息，请告诉我。
Ankit
安吉特
采纳答案by chris neilsen
Create a Name (like a named range, but refers to a value rather than a range.)
创建名称（类似于命名范围，但指的是值而不是范围。）
For Excel 2007 and 2010, on the Formulas tab, Defined Names group, Name Manager
For Excel 2003 Insert, Names, Define
对于 Excel 2007 和 2010，在“公式”选项卡上，“定义的名称”组，“名称管理器”
对于 Excel 2003 插入、名称、定义
Create a new Name (lets use the name "KeyWords") , set it "Refers To" value to
创建一个新名称（让我们使用名称“KeyWords”），将其“引用”值设置为
={"*costco*","*saputo*","*t & t*"}
Formula then becomes EDITenter as an array formula
公式然后变成 EDIT作为数组公式输入
=IF(SUM(COUNTIF($B10,KeyWords))>0,$C10," ")
Editing the list then becomes editing the value of KeyWords refers to value. This can be done through the Name Manager, or if you wish, through VBA
编辑列表然后变成编辑关键字的值指的是值。这可以通过名称管理器完成，或者如果您愿意，也可以通过 VBA
EDIT
编辑
Sample VBA
示例 VBA
Sub SetKeyWords()
Dim wb As Workbook
Dim nm As Name
Set wb = ActiveWorkbook
Set nm = wb.Names("KeyWords")
nm.RefersTo = "={""NewString""}"
End Sub
回答by jonsca
Load the strings you are seeking into an array (via converting a range into an array, or having the user select the strings from a small form you design)
将您要查找的字符串加载到数组中（通过将范围转换为数组，或让用户从您设计的小表单中选择字符串）
Sub MyLookup(arrCategories() As Variant)
Dim searchrange As Range
Dim xCell As Range
Dim count As Integer
'set searchrange to the proper column
For Each xCell In searchrange
For Each catg In arrCategories
If xCell = catg Then
'Do something here
End If
Next catg
Next xCell
End Sub
You may need to adjust your string array based on whether case matters. Depending on the size of your set of data, it might be better to convert searchrangeinto an array.
您可能需要根据大小写是否重要来调整字符串数组。根据数据集的大小，最好转换searchrange为数组。
There may be a faster way to see if certain strings are found within a given range, but this is the general idea.
可能有一种更快的方法来查看是否在给定范围内找到某些字符串，但这是总体思路。
